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;