How to Retrieve Data When Using PostgreSQL's ON CONFLICT DO NOTHING with RETURNING?

PostgreSQL's ON CONFLICT DO NOTHING and Data Retrieval

When using PostgreSQL's ON CONFLICT DO NOTHING clause with RETURNING, empty results are returned when conflicts occur. This is because DO NOTHING doesn't update or return any rows. This article explores solutions to retrieve data even when conflicts arise.

Challenges and Limitations of Existing Approaches

Existing methods often address this for single-conflict targets and simple conditions. However, these approaches can have limitations and potential side effects.

Robust Solutions for Data Retrieval

We present two improved approaches to handle conflicts effectively and ensure data retrieval, addressing concurrent write scenarios:

1. Handling Conflicts Without Concurrent Writes

This method uses a Common Table Expression (CTE) to separate INSERT and SELECT operations. The results are combined using 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>

This query first attempts to insert. Then, it selects existing rows matching the input data. The source column indicates whether a row was inserted ('i') or selected ('s').

2. Handling Conflicts With Concurrent Writes

This more robust solution accounts for concurrent write operations. It checks for missing rows within the query and uses an additional UPSERT to handle them.

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

This approach ensures all inserted or updated rows are returned, regardless of conflicts or concurrent updates. The ups CTE handles any rows missed in the initial INSERT. The source column distinguishes between inserted ('i'), selected ('s'), and updated ('u') rows. This provides a comprehensive solution for retrieving data under various conditions.

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