Получить идентификатор из условного INSERT

Реализация UPSERT чрезвычайно сложна для защиты от одновременного доступа к записи. Взгляните на эту вики Postgres, которая служила журналом во время первоначальной разработки. Хакеры Postgres решили не включать «исключенные» строки в предложение RETURNING для первого выпуска Postgres 9.5. Они могут что-то добавить для следующего релиза.

Это важное утверждение в руководстве, объясняющее вашу ситуацию:

Синтаксис списка RETURNING идентичен выходному списку SELECT. Будут возвращены только строки, которые были успешно вставлены или обновлены. Например, если строка была заблокирована, но не обновлена ​​из-за невыполнения условия пункта ON CONFLICT DO UPDATE ... WHERE, строка не будет возвращена.

Жирный акцент мой.

Чтобы вставить одну строку:

Без одновременной загрузки записи в одну и ту же таблицу

WITH ins AS (
   INSERT INTO users(name)
   VALUES ('new_usr_name')         -- input value
   ON     CONFLICT(name) DO NOTHING
   RETURNING users.id
   )
SELECT id FROM ins
UNION  ALL
SELECT id FROM users          -- 2nd SELECT never executed if INSERT successful
WHERE  name = 'new_usr_name'  -- input value a 2nd time
LIMIT  1;

С возможной параллельной записью в таблицу

Consider this instead (for single row INSERT):

• Является ли SELECT или INSERT в функция подвержена гонкам?

Чтобы вставить набор строк:

• Как использовать RETURNING с ON CONFLICT в PostgreSQL?

• Как включить исключенные строки в RETURNING from ВСТАВИТЬ... ПРИ КОНФЛИКТЕ

Все три с очень подробным объяснением.

Для вставки одной строки и без обновления:

with i as (
    insert into users (name)
    select 'the name'
    where not exists (
        select 1
        from users
        where name = 'the name'
    )
    returning id
)
select id
from users
where name = 'the name'

union all

select id from i

Руководство по основному и with подзапросам части:

Основной запрос и запросы WITH (условно) выполняются одновременно.

Хотя для меня это звучит как «тот же снимок», я не уверен, так как не знаю, что означает теоретически в этом контексте.

Но также есть:

Подоператоры в WITH выполняются одновременно друг с другом и с основным запросом. Таким образом, при использовании операторов, изменяющих данные, в WITH порядок, в котором на самом деле происходят указанные обновления, непредсказуем. Все операторы выполняются с одним и тем же моментальным снимком

Если я правильно понимаю, бит тот же снимок предотвращает состояние гонки. Но опять же я не уверен, относится ли все операторы только к операторам в with подзапросах, исключая основной запрос. Чтобы избежать сомнений, переместите выборку в предыдущем запросе в подзапрос with:

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 s
union all
select id from i