Return rows from INSERT with ON CONFLICT without needing to update Return rows from INSERT with ON CONFLICT without needing to update postgresql postgresql

Return rows from INSERT with ON CONFLICT without needing to update


It's the recurring problem of SELECT or INSERT, related to (but different from) an UPSERT. The new UPSERT functionality in Postgres 9.5 is still instrumental.

WITH ins AS (   INSERT INTO names(name)   VALUES ('bob')   ON     CONFLICT ON CONSTRAINT names_name_key DO UPDATE   SET    name = NULL   WHERE  FALSE      -- never executed, but locks the row   RETURNING id   )SELECT id FROM insUNION  ALLSELECT id FROM namesWHERE  name = 'bob'  -- only executed if no INSERTLIMIT  1;

This way you do not actually write a new row version without need.

I assume you are aware that in Postgres every UPDATE writes a new version of the row due to its MVCC model - even if name is set to the same value as before. This would make the operation more expensive, add to possible concurrency issues / lock contention in certain situations and bloat the table additionally.

However, there is still a tiny corner case for a race condition. Concurrent transactions may have added a conflicting row, which is not yet visible in the same statement. Then INSERT and SELECT come up empty.

Proper solution for single-row UPSERT:

General solutions for bulk UPSERT:

Without concurrent write load

If concurrent writes (from a different session) are not possible you don't need to lock the row and can simplify:

WITH ins AS (   INSERT INTO names(name)   VALUES ('bob')   ON     CONFLICT ON CONSTRAINT names_name_key DO NOTHING  -- no lock needed   RETURNING id   )SELECT id FROM insUNION  ALLSELECT id FROM namesWHERE  name = 'bob'  -- only executed if no INSERTLIMIT  1;