0

Suppose we have the following table:

CREATE TABLE names(
id SERIAL NOT NULL,
CONSTRAINT names__pk PRIMARY KEY(id),
name TEXT NOT NULL,
CONSTRAINT names__name__unq UNIQUE(name)
);

INSERT INTO names(name) 
VALUES('apple')
ON CONFLICT(name) DO NOTHING
RETURNING id;

When a row does not exist, RETURNING clause works, but if there already is a row, the query returns nothing. Is there a way to modify the query so that it always returns the field id?

1 Answer 1

1

The following single query could be used, to avoid a second query in most cases:

WITH res as (
 INSERT INTO names(name) 
  VALUES('apple')
  ON CONFLICT(name) DO NOTHING
  RETURNING id
)
SELECT id FROM res
 UNION ALL
SELECT id FROM names WHERE name='apple'
LIMIT 1; -- just in case: normally the two branches cannot both return 1 row

There's a caveat when executed concurrently with the same value. In the default Read Committed isolation mode, if session #1 runs this query but another session #2 has inserted the same value just before and not yet committed at the point of the INSERT of session #1, session #1 will not insert the value (so far so good), but the above query will return an empty result. The reason is that the INSERT ON CONFLICT can see the uncommitted session's value to detect the conflict, but the SELECT cannot see it.

To handle this on the client side, you may just retry the query whenever it returns nothing. As it should happen rarely, it's still an improvement in terms of running only one query most of the time, if that was your optimization goal.

In the stricter isolation modes "Repeatable Read" or "Serializable", the concurrency problem would be automatically detected and the transaction of session #1 would get canceled. In that case as well, you typically handle this by retrying.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.