RETURNING

Makes INSERT, UPDATE, DELETE and MERGE return the rows they changed.

Syntax

INSERT ... RETURNING *;
UPDATE ... RETURNING id, old.col AS before, new.col AS after;   -- old/new: PostgreSQL 18
DELETE ... RETURNING *;
WITH changed AS (UPDATE ... RETURNING ...) SELECT ... FROM changed;

RETURNING takes a select list: columns, expressions, *.
For INSERT it returns the stored values, including defaults and generated ids.
For UPDATE it returns the new values by default; since PostgreSQL 18, old.col and new.col give the before and after values (also in INSERT ... ON CONFLICT, DELETE and MERGE; old is NULL for inserted rows, new is NULL for deleted rows).
For DELETE it returns the removed rows — handy to archive them in the same statement via a data-modifying CTE.
Saves a second round trip and avoids race conditions between "change" and "read back".

Examples

Before and after values (modifies data) starter

UPDATE orders
SET status = 'shipped'
WHERE status = 'processing'
RETURNING order_id, old.status AS old_status, new.status AS new_status;

One row per updated order with its previous and new status.

Move rows with a data-modifying CTE (modifies data) starter

CREATE TABLE orders_archive (LIKE orders);

WITH moved AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM moved;

SELECT count(*) AS archived FROM orders_archive;

The deleted rows flow straight into the archive in one atomic statement.

Pitfalls

Expecting a SELECT in the same statement to see changes made inside a data-modifying CTE (all parts see the same snapshot; use the RETURNING output).
Using old./new. on PostgreSQL versions before 18.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

SQL Server: OUTPUT inserted.* / deleted.*.
MySQL: No RETURNING (MariaDB supports it on INSERT and DELETE).
SQLite: RETURNING since 3.35.
Oracle: RETURNING ... INTO variables in PL/SQL.