Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

INSERT ... ON CONFLICT ... RETURNING gives back no row for the existing record

ErrorAn empty RETURNING result when the row already exists

Short answer

RETURNING only returns rows that were actually inserted or updated. A row skipped by DO NOTHING, or left alone because the DO UPDATE WHERE condition was false, is not returned. Use DO UPDATE with a harmless SET so the row counts as updated, or look the row up in a second query.

  • Applies to PostgreSQL 15, PostgreSQL 16, PostgreSQL 17
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    Run the statement twice with the same key. The first call returns the row; if the second returns nothing, you are in the cases below.

    INSERT INTO users (email) VALUES ('a@example.com')
    ON CONFLICT (email) DO NOTHING
    RETURNING id;
  2. 2Match what you see, then jump to its fix

    1. 1The first call returns the id, the repeat call returns no row.DO NOTHING skips the row, so nothing is returnedFix
    2. 2You wrote DO UPDATE ... WHERE and the row exists but is not returned.A WHERE condition on DO UPDATE is falseFix
    3. 3A multi-row INSERT with DO UPDATE fails with a cardinality violation error.The same key appears twice in one multi-row insertFix

What does the error look like?

  • An empty RETURNING result when the row already exists
  • Application code gets no id back on the second call for the same key

Cause 1 of 3

DO NOTHING skips the row, so nothing is returned

1Check

The RETURNING clause computes values for each row actually inserted or updated, and only those. DO NOTHING avoids the insert, so there is no row to return.

SELECT id FROM users WHERE email = 'a@example.com';

2Fix

Turn the conflict into an update that changes nothing real. The row is then updated and comes back through RETURNING.

INSERT INTO users (email) VALUES ('a@example.com')
ON CONFLICT (email) DO UPDATE SET email = EXCLUDED.email
RETURNING id;

3Verify

Run it twice. Both calls return the same id.

SELECT count(*) FROM users WHERE email = 'a@example.com';

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 3

A WHERE condition on DO UPDATE is false

1Check

The row is locked but not updated when the condition is not satisfied, and the documentation says such a row is not returned.

grep -rn "ON CONFLICT" ./migrations ./src

2Fix

Remove the WHERE if you always want the row back, or fetch the row separately when the condition fails.

INSERT INTO counters (key, hits) VALUES ('home', 1)
ON CONFLICT (key) DO UPDATE SET hits = counters.hits + 1
RETURNING key, hits;

3Verify

Each call returns the row.

SELECT * FROM counters WHERE key = 'home';

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 3

The same key appears twice in one multi-row insert

1Check

A DO UPDATE statement may not affect any single existing row more than once, so rows proposed for insertion must not duplicate each other on the arbiter columns.

SELECT email, count(*) FROM staging_users GROUP BY email HAVING count(*) > 1;

2Fix

Remove duplicates from the batch before the insert, for example with DISTINCT ON, then run the upsert.

INSERT INTO users (email, name)
SELECT DISTINCT ON (email) email, name FROM staging_users ORDER BY email
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name
RETURNING id;

3Verify

The statement completes and returns one row per distinct email.

SELECT count(DISTINCT email) FROM staging_users;

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Decide per query whether you need the row back; if you do, use DO UPDATE and not DO NOTHING.
  • Deduplicate batch input on the conflict columns before the statement runs.
  • Remember a no-op DO UPDATE still counts as an update, so use it only where that is acceptable.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.