How to Reliably Use PostgreSQL's RETURNING Clause with ON CONFLICT DO NOTHING?

PostgreSQL's RETURNING Clause and ON CONFLICT DO NOTHING

PostgreSQL's RETURNING clause is invaluable for retrieving data from newly inserted rows after an INSERT operation. This is especially useful with UPSERTs (insert or update) using ON CONFLICT. A common challenge arises when using ON CONFLICT DO NOTHING: if no conflict exists, RETURNING yields no results.

While attempting to update matching rows to trigger RETURNING might seem like a solution, this is strongly discouraged. The potential drawbacks include:

• Unnecessary trigger activations
• Unintentional write-locking of rows
• Misrepresenting existing rows as new
• Performance degradation due to excessive row versioning

More efficient and robust alternatives exist.

Handling Scenarios Without Concurrent Writes:

For environments without significant concurrent write activity, a CTE (Common Table Expression) combined with a final JOIN provides an elegant solution. This ensures both inserted and existing rows are included in the output:

<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>

Addressing Concurrent Write Loads:

Concurrent write operations introduce complexity. If another transaction modifies a row before your INSERT completes, inconsistencies can arise.

To mitigate this, consider locking existing rows early. A second UPSERT step can handle any missing rows:

<code class="language-sql">WITH input_rows(usr, contact, name) AS ( ... )
, ins AS (
   INSERT INTO chats AS c (usr, contact, name) 
   SELECT * FROM input_rows
   ON     CONFLICT (usr, contact) DO UPDATE
   SET    name = name WHERE FALSE  -- Locks the row without updating
   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 AS c (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
   RETURNING 'u' AS source, id
   )
SELECT source, id FROM sel
UNION  ALL
TABLE  ups;</code>

These approaches reliably return data for both inserted and existing rows, even under concurrent write pressure.

The above is the detailed content of How to Reliably Use PostgreSQL's RETURNING Clause with ON CONFLICT DO NOTHING?. For more information, please follow other related articles on the PHP Chinese website!