Comment récupérer les identifiants des lignes insérées et en conflit à l'aide de ON CONFLICT de PostgreSQL ?

Récupération des identifiants des UPSERT PostgreSQL avec ON CONFLICT

Défi : Standard PostgreSQL 9.5 ON CONFLICT DO NOTHING avec RETURNING id ne renvoie pas les identifiants des lignes en conflit. L'objectif est de récupérer les ID des lignes nouvellement insérées et des lignes existantes impliquées dans des conflits lors d'une opération d'upsert.

Solution :

Cette approche améliorée utilise une clause WITH pour gérer efficacement ceci :

<code class="language-sql">WITH existing_rows AS (
    SELECT id
    FROM chats
    WHERE ("user", "contact") = ANY (SELECT ("user", "contact") FROM temp_table)
), inserted_rows AS (
    INSERT INTO chats ("user", "contact", "name")
    SELECT "user", "contact", "name"
    FROM temp_table
    ON CONFLICT ("user", "contact") DO NOTHING
    RETURNING id
)
SELECT id FROM existing_rows
UNION ALL
SELECT id FROM inserted_rows;</code>

temp_table contient les données à insérer.

Explication :

1. existing_rows CTE : Ceci sélectionne les id des lignes préexistantes dans le tableau chats qui correspondent aux ("user", "contact") paires dans temp_table.

2. inserted_rows CTE : Ceci exécute l'instruction INSERT avec ON CONFLICT DO NOTHING et RETURNING id, capturant les id des lignes nouvellement insérées.

3. UNION ALL : Ceci combine les résultats des deux CTE, fournissant une liste complète des id affectés par l'opération upsert.

Considérations importantes :

• Concurrence : Dans les environnements à forte concurrence, les données peuvent changer entre les opérations SELECT et INSERT. Envisagez d'utiliser des transactions ou des mécanismes de contrôle de concurrence plus robustes pour l'intégrité des données.
• Types de données : Assurez-vous que les types de données dans temp_table correspondent à ceux du tableau chats. Un casting explicite peut être nécessaire.
• Unicité : Conservez l'unicité des données de temp_table (ou utilisez un identifiant unique) pour éviter plusieurs mises à jour involontaires de la même ligne.

Cette solution raffinée offre une méthode plus claire et plus efficace pour récupérer tous les identifiants pertinents lors d'une opération d'upsert PostgreSQL avec gestion des conflits.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!