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

Question

Для такой таблицы:

CREATE TABLE Users(
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE
);

Какова будет правильная вставка в один запрос для следующей операции:

Данный пользователь name, вставьте новую запись и верните новую id, Но если name уже существует, просто верните id,

Я знаю о новом синтаксисе в PostgreSQL 9.5 для ON CONFLICT(column) DO UPDATE/NOTHING, но я не могу понять, как, если вообще, это может помочь, учитывая, что мне нужно id быть возвращенным.

Кажется, что RETURNING id а также ON CONFLICT не принадлежат друг другу.

Answer 1

Реализация UPSERT чрезвычайно сложна, чтобы быть защищенной от одновременного доступа к записи. Взгляните на эту Postgres Wiki, которая использовалась в качестве журнала при начальной разработке. Хакеры 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 UPDATE
   SET    name = name WHERE FALSE  -- never executed, just to lock row
   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;

Или заключите в функцию, чтобы указать новое имя только один раз. Как показано здесь (также рассмотрим объяснение LIMIT 1):

• SELECT или INSERT в функции склонны к условиям гонки?

Возможная гонка: параллельная транзакция может изменить / удалить существующую строку между INSERT попытка и SELECT, Весьма маловероятно, но возможно.

Если у вас нет (возможно) одновременного доступа к записи (или вам все равно), упростите:

...
ON     CONFLICT(name) DO NOTHING
...

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

• Как включить исключенные строки в RETURNING из INSERT ... ON CONFLICT

Answer 2

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

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