Get Id from a conditional INSERT
The UPSERT implementation is hugely complex to be safe against concurrent write access. Take a look at this Postgres Wiki that served as log during initial development. The Postgres hackers decided not to include "excluded" rows in the
RETURNING clause for the first release in Postgres 9.5. They might build something in for the next release.
This is the crucial statement in the manual to explain your situation:
The syntax of the
RETURNINGlist is identical to that of the output list of
SELECT. Only rows that were successfully inserted or updated will be returned. For example, if a row was locked but not updated because an
ON CONFLICT DO UPDATE ... WHEREclause condition was not satisfied, the row will not be returned.
Bold emphasis mine.
For a single row to insert:
Without concurrent write load on the same table
WITH ins AS ( INSERT INTO users(name) VALUES ('new_usr_name') -- input value ON CONFLICT(name) DO NOTHING RETURNING users.id )SELECT id FROM insUNION ALLSELECT id FROM users -- 2nd SELECT never executed if INSERT successfulWHERE name = 'new_usr_name' -- input value a 2nd timeLIMIT 1;
With possible concurrent write load on the table
Consider this instead (for single row
To insert a set of rows:
All three with very detailed explanation.
For a single row insert and no update:
with i as ( insert into users (name) select 'the name' where not exists ( select 1 from users where name = 'the name' ) returning id)select idfrom userswhere name = 'the name'union allselect id from i
The manual about the primary and the
with subqueries parts:
The primary query and the WITH queries are all (notionally) executed at the same time
Although that sounds to me "same snapshot" I'm not sure since I don't know what notionally means in that context.
But there is also:
The sub-statements in WITH are executed concurrently with each other and with the main query. Therefore, when using data-modifying statements in WITH, the order in which the specified updates actually happen is unpredictable. All the statements are executed with the same snapshot
If I understand correctly that same snapshot bit prevents a race condition. But again I'm not sure if by all the statements it refers only to the statements in the
with subqueries excluding the main query. To avoid any doubt move the select in the previous query to a
with s as ( select id from users where name = 'the name'), i as ( insert into users (name) select 'the name' where not exists (select 1 from s) returning id)select id from sunion allselect id from i