Как получить идентификатор вставленной строки при использовании upsert с предложением WITH в Posgres 9.5?

Я пытаюсь выполнить расстроенный запрос в Postgres 9.5, используя «WITH».

with s as (
            select id
            from products
            where product_key = 'test123'
        ), i as (
            insert into products (product_key, count_parts)
            select 'test123', 33
            where not exists (select 1 from s)
            returning id                       
        )
        update products
        set product_key='test123', count_parts=33
        where id = (select id from s) 
        returning id

По-видимому, я получаю идентификатор только для обновлений и ничего не получаю при вставках, хотя я знаю, что вставки прошли успешно.

Мне нужно изменить этот запрос таким образом, чтобы я мог получить идентификатор как при вставках, так и при обновлениях.

Спасибо!


postgresql sql postgresql-9.5
person Yuval Kaufman    schedule 18.08.2016    source источник
comment
Это не апсерт. postgresql.org/docs/9.5/static/ sql-insert.html#SQL-ON-CONFLICT   -  person e4c5    schedule 18.08.2016
comment
Обновите в другом CTE, затем получите идентификаторы в окончательном выборе.   -  person Abelisto    schedule 18.08.2016

Ответы (1)


arrow_upward
1
arrow_downward

Мне было непонятно, почему вы делаете это в WITH first SELECT, но причина, по которой вы получаете только возврат UPDATE id, заключается в том, что вы не выбираете возврат INSERT.

Как уже упоминалось (и ссылка) в комментариях, Postgres 9.5 поддерживает предложение INSERT ON CONFLICT, которое является гораздо более чистым способом использования.

И несколько примеров до и после 9.5:

До 9.5: распространенный способ использования WITH

WITH    u AS (
            UPDATE      products
            SET         product_key='test123', count_parts=33
            WHERE       product_key = 'test123'
            RETURNING   id
        ),i AS (
            INSERT
            INTO        products ( product_key, count_parts )
            SELECT      'test123', 33
            WHERE       NOT EXISTS( SELECT 1 FROM u )
            RETURNING   id
        )
SELECT  *
FROM    (       SELECT id FROM u
        UNION   SELECT id FROM i
        ) r;

После 9.5: использование INSERT .. ON CONFLICT

INSERT INTO products ( product_key, count_parts )
VALUES      ( 'test123', 33 )
ON CONFLICT ( product_key ) DO
UPDATE
SET         product_key='test123', count_parts=33
RETURNING   id;

ОБНОВЛЕНИЕ:

Как намекается в комментарии, могут быть небольшие минусы при использовании INSERT .. ON CONFLICT способа.
Если таблица использует автоинкремент и этот запрос выполняется часто, то WITH может быть лучшим вариантом.
Подробнее: https://stackoverflow.com/a/39000072/1161463

person Kristo Mägi    schedule 18.08.2016
comment
В 9.5, если вы используете INSERT ... ON CONFLICT UPDATE, в первичном ключе автоинкремента в таблице будут появляться все большие пробелы. Это может привести к тому, что вы довольно быстро выйдете за пределы диапазона значений приращения, если ваша таблица выполняет много UPSERT способом 9.5. К сожалению, сейчас WITH кажется единственным решением. stackoverflow.com/a/39000072/1161463 - person eComEvo; 18.08.2016
comment
@eComEvo: это интересно и полезно знать — спасибо за подсказку. В таком случае я согласен, продолжайте использовать WITH - person Kristo Mägi; 18.08.2016
comment
Имейте в виду, что решение WITH чувствительно к условиям гонки. - person Jim Nasby; 18.08.2016