PostgreSQL - CTE upsert returning modified rows

Question

I've written an 'upsert' query using CTEs that looks something like this:

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
  )

However I then need to work with the new values in my application, but this query will not return anything. Adding returning * to the end of the insert will return all the rows that were inserted, but none of the rows that were updated.

So, the question is (how) can I extend this to return the rows that were updated AND the rows that were inserted?

EDIT: of course I could run this followed by a SELECT in a transaction, however I'm curious to see if there's a single-query way.

score 9 · Accepted Answer · answered Jul 10 '13 at 16:07

9

Try something like:

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

BTW this query is not a classical Postgres upsert. It will fail, if someone concurrently insert rows while UPDATE table t is going.

answered Jul 10 '13 at 16:07

Ihor Romanchenko

26,995
8
48
44

Thanks Igor, I did wonder about `UNION`, and that indeed seems to work. Will this result in the 'updated' query being run twice, though? (My understanding of CTEs is a little shaky, but I guess they're run once and remembered?) – connec Jul 10 '13 at 16:23
Also, could you elaborate on your statement "BTW this query is not a classical Postgres upsert"? This is the fashion of a lot of queries purported to be CTE-driven upserts. – connec Jul 10 '13 at 16:24
2

@connec: "common tables" are run *once* and the result is materialized in an internal worktable. – Erwin Brandstetter Jul 10 '13 at 16:37
2

@connec Read answers and comments to [`this`](http://stackoverflow.com/questions/1109061/insert-on-duplicate-update-postgresql) questions. This CTE can fail in heavily concurrent environment. (but the chanses of failing are low) – Ihor Romanchenko Jul 10 '13 at 16:41

PostgreSQL - CTE upsert returning modified rows

1 Answers1