sql - Return rows from INSERT with ON CONFLICT without needing to update

Question

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:

1. What is the reasoning for not allowing the (my) desired behaviour?

2. Is there a more elegant way to do this?

Answer 1

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;