Comment récupérer des données lors de l'utilisation de ON CONFLICT DO RIEN avec RETURNING de PostgreSQL ?

ON CONFLICT DO NOTHING de PostgreSQL et récupération de données

Lors de l'utilisation de la clause ON CONFLICT DO NOTHING de PostgreSQL avec RETURNING, des résultats vides sont renvoyés lorsque des conflits surviennent. En effet, DO NOTHING ne met pas à jour et ne renvoie aucune ligne. Cet article explore des solutions pour récupérer des données même en cas de conflits.

Défis et limites des approches existantes

Les méthodes existantes abordent souvent ce problème pour des cibles à conflit unique et des conditions simples. Cependant, ces approches peuvent avoir des limites et des effets secondaires potentiels.

Solutions robustes pour la récupération de données

Nous présentons deux approches améliorées pour gérer efficacement les conflits et garantir la récupération des données, en traitant des scénarios d'écriture simultanés :

1. Gérer les conflits sans écritures simultanées

Cette méthode utilise une expression de table commune (CTE) pour séparer les opérations INSERT et SELECT. Les résultats sont combinés à l'aide de UNION ALL.

<code class="language-sql">WITH input_rows(usr, contact, name) AS (
   VALUES
      ('foo1', 'bar1', 'bob1')
    , ('foo2', 'bar2', 'bob2')
    -- more?
   )
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id
   )
SELECT 'i' AS source, id
FROM   ins
UNION  ALL
SELECT 's' AS source, c.id
FROM   input_rows
JOIN   chats c USING (usr, contact);</code>

Cette requête tente d'abord d'être insérée. Ensuite, il sélectionne les lignes existantes correspondant aux données d'entrée. La colonne source indique si une ligne a été insérée (« i ») ou sélectionnée (« s »).

2. Gestion des conflits avec les écritures simultanées

Cette solution plus robuste prend en compte les opérations d'écriture simultanées. Il vérifie les lignes manquantes dans la requête et utilise un UPSERT supplémentaire pour les gérer.

<code class="language-sql">WITH input_rows(usr, contact, name) AS ( ... )  -- as above
, ins AS (
   INSERT INTO chats (usr, contact, name) 
   SELECT * FROM input_rows
   ON CONFLICT (usr, contact) DO NOTHING
   RETURNING id, usr, contact
   )
, sel AS (
   SELECT 'i' AS source, id, usr, contact
   FROM   ins
   UNION  ALL
   SELECT 's' AS source, c.id, usr, contact
   FROM   input_rows
   JOIN   chats c USING (usr, contact)
   )
, ups AS (
   INSERT INTO chats (usr, contact, name)
   SELECT i.*
   FROM   input_rows i
   LEFT   JOIN sel s USING (usr, contact)
   WHERE  s.usr IS NULL
   ON CONFLICT (usr, contact) DO UPDATE
   SET    name = c.name  -- or EXCLUDED.name
   RETURNING 'u' AS source, id
   )
SELECT source, id FROM sel
UNION  ALL
SELECT * FROM ups;</code>

Cette approche garantit que toutes les lignes insérées ou mises à jour sont renvoyées, quels que soient les conflits ou les mises à jour simultanées. Le ups CTE gère toutes les lignes manquées dans le INSERT initial. La colonne source fait la distinction entre les lignes insérées (« i »), sélectionnées (« s ») et mises à jour (« u »). Cela fournit une solution complète pour récupérer des données dans diverses conditions.

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!