TopolearnRETURNING saves a round trip and avoids race-prone 'insert then select' code.
PostgreSQL's RETURNING clause makes data-modifying statements return rows, like a SELECT of what they just changed:
INSERT ... RETURNING idgives you generated keys and defaults without a second query.UPDATE ... RETURNING *shows the new values after the update.DELETE ... RETURNING *returns what was removed — handy for moving rows to an archive table.
PostgreSQL 18 also lets you write RETURNING old.col, new.col to see both the before and after values of an update.
Combined with upsert (INSERT ... ON CONFLICT), it becomes very expressive. ON CONFLICT (key) DO UPDATE SET col = EXCLUDED.col inserts a new row or updates the existing one atomically; EXCLUDED refers to the row you tried to insert. ON CONFLICT DO NOTHING skips duplicates — but note that then RETURNING returns no row for the skipped ones.
Data-modifying statements can also sit inside a WITH clause, and the outer query can use their RETURNING output. That lets you, for example, delete old rows and insert them into an archive in a single statement.
ON CONFLICT needs a unique index or constraint on the conflict target columns; it is the safe, concurrent alternative to "SELECT, and INSERT if missing".
Example
CREATE TABLE stock (sku text PRIMARY KEY, qty int NOT NULL);
CREATE TABLE stock_archive (sku text, qty int, archived_at timestamptz DEFAULT now());
INSERT INTO stock VALUES ('A1', 5) RETURNING *; -- A1 | 5
INSERT INTO stock VALUES ('A1', 3)
ON CONFLICT (sku) DO UPDATE SET qty = stock.qty + EXCLUDED.qty
RETURNING sku, qty; -- A1 | 8
UPDATE stock SET qty = 0 WHERE sku = 'A1' RETURNING qty; -- 0
WITH gone AS (
DELETE FROM stock WHERE qty = 0 RETURNING sku, qty
)
INSERT INTO stock_archive (sku, qty) SELECT sku, qty FROM gone
RETURNING sku; -- A1Remember
- RETURNING turns INSERT/UPDATE/DELETE into row-producing statements
- ON CONFLICT ... DO UPDATE with EXCLUDED is an atomic upsert
- Data-modifying CTEs chain changes in one statement
Watch out for
- Expecting RETURNING to produce a row for inputs skipped by ON CONFLICT DO NOTHING.