Return rows from INSERT with ON CONFLICT without needing to update

postgresql, postgresql-9.5, sql, upsert

Solution

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:

- Is SELECT or INSERT in a function prone to race conditions?

General solutions for bulk UPSERT:

- How to use RETURNING with ON CONFLICT in PostgreSQL?

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;

Problem

I have a situation where I very frequently need to get a row from a table with a unique constraint, and if none exists then create it and return. For example my table might be: ``` CREATE TABLE names( id SERIAL PRIMARY KEY, name TEXT, CONSTRAINT names_name_key UNIQUE (name) ); ``` And it contains: ``` id | name 1 | bob 2 | alice ``` Then I'd like to: ``` INSERT INTO names(name) VALUES ('bob') ON CONFLICT DO NOTHING RETURNING id; ``` Or perhaps: ``` INSERT INTO names(name) VALUES ('bob') ON CONFLICT (name) DO NOTHING RETURNING id ``` and have it return bob's id `1`. However, `RETURNING` only returns either inserted or updated rows. So, in the above example, it wouldn't return anything. In order to have it function as desired I would actually need to: ``` INSERT INTO names(name) VALUES ('bob') ON CONFLICT ON CONSTRAINT names_name_key DO UPDATE SET name = 'bob' RETURNING id; ``` which seems kind of cumbersome. I guess my questions are: What is the reasoning for not allowing the (my) desired behaviour? Is there a more elegant way to do this?

Original source

Related problems