Fun SQL trick (UPSERT with output containing INSERTed / UPDATEd rows)

[dhermes]

It's not THAT bad with a CTE (though I'm not sure if there are implicit race conditions in here on when the SELECT in the first CTE runs):

BEGIN;

CREATE TABLE app.foo (id INTEGER PRIMARY KEY NOT NULL, v TEXT NOT NULL);
INSERT INTO app.foo (id, v) VALUES (1, 'a'), (3, 'b'), (5, 'c');
SELECT * FROM app.foo;

WITH f_previous AS (
  SELECT * FROM app.foo WHERE id IN (1, 2, 3, 4)
), f_next AS (
  INSERT INTO
    app.foo (id, v)
  VALUES
    (1, 'x'),
    (2, 'y'),
    (3, 'z'),
    (4, 't')
  ON CONFLICT (id)
    DO UPDATE
      SET v = EXCLUDED.v
  RETURNING
    id, v
)

SELECT
  f_next.*,
  '::' AS sep,
  f_previous.*
FROM
  f_next
  LEFT JOIN f_previous ON f_previous.id = f_next.id;

ROLLBACK;

produces

$ psql --dbname postgres://... --file ./tmp.sql
BEGIN
CREATE TABLE
INSERT 0 3
 id | v 
----+---
  1 | a
  3 | b
  5 | c
(3 rows)

 id | v | sep | id | v 
----+---+-----+----+---
  1 | x | ::  |  1 | a
  3 | z | ::  |  3 | b
  2 | y | ::  |    | 
  4 | t | ::  |    | 
(4 rows)

ROLLBACK

Hat tip to https://stackoverflow.com/a/70312948/1068170