PostgreSQL - CTE upsert returning modified rows
common-table-expression, postgresql, postgresql-9.1, sql, upsert
Solution
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.
Problem
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.