PostgreSQL - CTE upsert returning modified rows
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, valueFROM inserted UNION ALLSELECT id, valueFROM updated BTW this query is not a classical Postgres upsert. It will fail, if someone concurrently insert rows while UPDATE table t is going.