Return rows from INSERT with ON CONFLICT without needing to update

by

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 ins
UNION  ALL
SELECT id FROM names
WHERE  name="bob"  -- only executed if no INSERT
LIMIT  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 ins
UNION  ALL
SELECT id FROM names
WHERE  name="bob"  -- only executed if no INSERT
LIMIT  1;

More Related Contents:

  1. How to use RETURNING with ON CONFLICT in PostgreSQL?
  2. Is SELECT or INSERT in a function prone to race conditions?
  3. Optimize query with OFFSET on large table
  4. Generate DEFAULT values in a CTE UPSERT using PostgreSQL 9.3
  5. How to include excluded rows in RETURNING from INSERT … ON CONFLICT
  6. Why isn’t row level security enabled for Postgres views?
  7. What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
  8. How to declare a variable in a PostgreSQL query
  9. Difference between language sql and language plpgsql in PostgreSQL functions
  10. Query with LEFT JOIN not returning rows for count of 0
  11. How to include “zero” / “0” results in COUNT aggregate?
  12. How do I force Postgres to use a particular index?
  13. delete “column does not exist”
  14. How to create a temporary function in PostgreSQL?
  15. Polymorphism in SQL database tables?
  16. Query for element of array in JSON column
  17. INSERT rows into multiple tables in a single query, selecting from an involved table
  18. Casting NULL type when updating multiple rows
  19. How to round an average to 2 decimal places in PostgreSQL?
  20. Best way to delete millions of rows by ID
  21. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  22. postgresql: INSERT INTO … (SELECT * …)
  23. How to read the contents of an .sql file into an R script to run a query?
  24. INSERT COMMAND :: ERROR: column “value” does not exist
  25. Return multiple columns and rows from a function PostgreSQL instead of record
  26. Postgres error updating column data
  27. SQL LIKE condition to check for integer?
  28. Counting the number of occurrences of a substring within a string in PostgreSQL
  29. Operator does not exist: json = json
  30. Is there a postgres CLOSEST operator?

Leave a Comment