RETURNING and Data-Modifying CTEs

A write statement in Postgres can have a result set. RETURNING hands back the generated id without a second query, the rows an UPDATE actually touched rather than a count of them, and since 18 the OLD and NEW versions of the same row side by side. It applies to INSERT, UPDATE, DELETE and MERGE alike.

Put that together with a WITH clause containing writes and one statement can delete rows from one table, insert them into another, and report what it moved — atomically, in one round trip, with no explicit transaction. Cartwheel needs exactly that: checkout currently issues an insert and then a select to learn the order id, and the nightly archive job moves rows out of delivery_events with three statements and a window in which a row exists in both tables.

RETURNING on INSERT, UPDATE, and DELETE

The familiar use is reading back a value the database generated. The more valuable one is knowing which rows a statement changed. A command tag says seventeen rows were updated; it does not say which seventeen, and for any conditional update that difference is the entire audit trail. DELETE … RETURNING is the same idea for rows that no longer exist to be queried afterwards.

INSERT INTO orders (public_id, customer_id, placed_at, status, total)
VALUES (uuidv7(), 88123, now(), 'pending', 41.80)
RETURNING id, public_id, placed_at;

UPDATE orders SET status = 'dispatched'
 WHERE status = 'picking'
   AND placed_at < now() - interval '20 minutes'
RETURNING id, customer_id;          -- exactly which orders moved

The insert returns the sequence-assigned id and the public_id the customer will see, both computed inside the statement that wrote them, and on the checkout path that removes a full network latency from every order at Saturday peak. The update returns the identity of every order it dispatched, so the job that sends notifications works from the list the database produced rather than re-deriving it with a second query against a table that has meanwhile changed.

OLD and NEW, since 18

Version 18 added the ability to return both versions of a modified row. Write old.column and new.column in the RETURNING list and both are available; new.price means the same as writing price bare, and saying it explicitly is what makes the other half readable. An alias form exists — RETURNING WITH (OLD AS o, NEW AS n) o.*, n.* — and it is optional, there for cases where the names would collide with something in the query.

INSERT INTO inventory (product_id, warehouse_id, on_hand)
VALUES (4471, 2, 18)
    ON CONFLICT (product_id, warehouse_id) DO UPDATE
   SET on_hand = EXCLUDED.on_hand
RETURNING product_id, warehouse_id,
          old.on_hand AS was, new.on_hand AS now_holds;

For a plain insert the old values are all NULL, and for a delete the new values are; the interesting cases are the ones in between. In an upsert, old.on_hand IS NULL is precisely the test for "this row was inserted, not updated", which previously required either a trigger or a separate lookup — a full before-and-after audit line, without a line of trigger code. The same syntax works in MERGE, where 17's merge_action() already reports which branch fired.

Data-Modifying CTEs

A WITH clause may contain INSERT, UPDATE, DELETE and MERGE, provided the WITH is attached to the top-level statement. A branch that has no RETURNING clause forms no temporary table and cannot be referred to elsewhere in the query — it simply runs. And every data-modifying branch executes exactly once and always to completion, whether or not the main query reads a single row of its output.

WITH batch AS (
    SELECT id FROM delivery_events
     WHERE occurred_at < now() - interval '90 days'
     ORDER BY id
     LIMIT 50000
), moved AS (
    DELETE FROM delivery_events d
     USING batch b
     WHERE d.id = b.id
    RETURNING d.*
)
INSERT INTO delivery_events_archive
SELECT * FROM moved;

The first branch picks a bounded set of old event ids. The second deletes exactly those rows and returns them. The main statement inserts what the delete handed over. As one statement it is atomic by construction, and the rows are never in both tables and never in neither. Three statements written separately would need an explicit transaction to be equally safe, and a caller that cannot hold one — a pooled connection in transaction mode, a serverless worker — cannot write them safely at all.

The Snapshot Rule

Every sub-statement in a data-modifying WITH runs against the same snapshot, so none of them can see another's effects on the target tables. RETURNING data is the only channel between branches.

WITH bumped AS (
    UPDATE products SET price = price * 1.05 RETURNING *
)
SELECT id, price FROM products;   -- the prices BEFORE the update

WITH bumped AS (
    UPDATE products SET price = price * 1.05 RETURNING *
)
SELECT id, price FROM bumped;     -- the prices AFTER it

Reading the target table directly gives the pre-update rows, because the main query's snapshot was taken before the branch ran and does not move. Reading the branch's own output gives the new ones. Neither is a bug; they are two different questions. The corollaries are stricter than they look: the order in which branches execute is unpredictable, they run concurrently with each other and with the main query, and a statement is not permitted to update the same row twice — only one of the modifications happens, and which one is not something you can reliably predict. Delete a row another branch already updated, and only the update takes place.

One Statement, One Round Trip

Composition is where this stops being a trick and becomes a tool. A branch that inserts a parent row and returns its id can feed the insert of the children in the same statement, so the application never learns the id at all and never has to send it back.

WITH new_order AS (
    INSERT INTO orders (public_id, customer_id, placed_at, status, total)
    VALUES (uuidv7(), 88123, now(), 'pending', 41.80)
    RETURNING id
)
INSERT INTO order_items (order_id, product_id, qty, unit_price)
SELECT n.id, c.product_id, c.qty, c.unit_price
  FROM new_order n
  CROSS JOIN jsonb_to_recordset(:cart)
       AS c(product_id bigint, qty int, unit_price numeric(10,2))
RETURNING order_id, product_id, qty;

One statement, one transaction, one network hop, and no intermediate state the application has to hold. That last property is what makes the pattern matter beyond elegance — Chapter 10 makes the constraint concrete when Cartwheel puts PgBouncer in front of 400 application connections. A caller behind a pooler running in transaction mode does not own a session between statements, so anything that needs two statements to be atomic needs an explicit transaction the pooler will not give it cheaply.

Where It Is the Wrong Tool

The LIMIT 50000 on the archiving batch is the design rather than caution. Ninety days of delivery_events is roughly 360 million rows, and moving them in a single statement is a single transaction holding a single snapshot for however long it runs: the vacuum horizon is pinned cluster-wide for the duration, the WAL arrives at pg-replica-a as one uninterruptible flood, and an interruption at minute fifty rolls all of it back. Bounded batches with a commit between them are boring, resumable, and the same discipline the backfill in Chapter 3 used.

There is also a version of this problem that better tooling deletes entirely. Chapter 11 partitions delivery_events by month, at which point archiving a month is ALTER TABLE … DETACH PARTITION — a catalogue operation that moves no rows, writes no WAL for the data, and finishes in milliseconds. That is the shape of the second half of this book: the developer half ends here, with a schema that holds its shape and SQL that asks for what it needs in one pass. What it cannot yet explain is why any of it costs what it costs — why an update leaves debris, why the same query is fast on Tuesday and slow on the first Saturday of the month, why 12,000 inventory rows occupy 900 MB. Chapter 5 opens the file and looks at the page.