PostgreSQL - CTE upsert, возвращающий измененные строки

Я написал запрос upsert с использованием CTE, который выглядит примерно так:

WITH
  new_data (id, value) AS (
    VALUES (1, 2), (3, 4), ...
  ),
  updated AS (
    UPDATE table t set
      value = t.value + new_data.value
    FROM new_data
    WHERE t.id = new_data.id
    RETURNING t.*
  )
INSERT INTO table (id, value)
  SELECT id, value
  FROM new_data
  WHERE NOT EXISTS (
    SELECT 1 FROM updated WHERE updated.id = new_data.id
  )

Однако затем мне нужно работать с новыми значениями в моем приложении, но этот запрос ничего не вернет. Добавление returning * в конец вставки вернет все вставленные строки, но ни одну из обновленных строк.

Итак, вопрос в том (как) я могу расширить это, чтобы вернуть строки, которые были обновлены, И строки, которые были вставлены?

EDIT: конечно, я мог бы запустить это, за которым следует SELECT в транзакции, однако мне любопытно посмотреть, есть ли способ с одним запросом.


postgresql sql common-table-expression upsert postgresql-9.1
person connec    schedule 10.07.2013    source источник

Ответы (1)


arrow_upward
9
arrow_downward

Попробуйте что-то вроде:

WITH
  new_data (id, value) AS (
    VALUES (1, 2), (3, 4), ...
  ),
  updated AS (
    UPDATE table t set
      value = t.value + new_data.value
    FROM new_data
    WHERE t.id = new_data.id
    RETURNING t.*
  ),
  inserted as (
  INSERT INTO table (id, value)
  SELECT id, value
  FROM new_data
  WHERE NOT EXISTS (
    SELECT 1 FROM updated WHERE updated.id = new_data.id
  )
  RETURNING id, value)
SELECT id, value
FROM inserted 
UNION ALL
SELECT id, value
FROM updated

Кстати, этот запрос не является классическим upsert Postgres. Это не удастся, если кто-то одновременно вставит строки, пока идет UPDATE table t.

person Ihor Romanchenko    schedule 10.07.2013
comment
Спасибо, Игорь, я действительно задавался вопросом о UNION, и это действительно работает. Приведет ли это к тому, что «обновленный» запрос будет выполняться дважды? (Мое понимание CTE немного шаткое, но я предполагаю, что они запускаются один раз и запоминаются?) - person connec; 10.07.2013
comment
Кроме того, не могли бы вы уточнить свое утверждение, кстати, этот запрос не является классическим upsert Postgres? Это мода многих запросов, которые якобы являются CTE-управляемыми upserts. - person connec; 10.07.2013
comment
@connec: общие таблицы запускаются один раз, и результат материализуется во внутренней рабочей таблице. - person Erwin Brandstetter; 10.07.2013
comment
@connec Читайте ответы и комментарии на вопросы this. Этот CTE может дать сбой в многопараллельной среде. (но вероятность неудачи мала) - person Ihor Romanchenko; 10.07.2013