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.

Answer 1

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.