The RETURNING Clause

RETURNING can be appended to INSERT, UPDATE, and DELETE to get back the rows that statement just affected, in the same round trip, without a separate SELECT afterward. This is a genuinely distinctive PostgreSQL feature — most databases either lack an equivalent entirely or offer something far more limited — and using it well removes a surprising amount of boilerplate from application code.

Why it matters

Without RETURNING, getting information about a row you just changed means running a second statement: insert, then separately query for the id that was generated; update, then separately query to see the new values; delete, then hope you remembered to look at the row before removing it. RETURNING collapses each of those into one statement and one round trip to the database.

Getting a generated id back from INSERT

The most common use of RETURNING is grabbing an auto-generated primary key (from SERIAL, IDENTITY, or a default like gen_random_uuid()) immediately after inserting, so the application can use that id right away — to insert related rows, or to return it in an API response.

Getting the new customer_id in one statement

SQL

INSERT INTO customers (name, email)
VALUES ('Priya Nair', 'priya@example.com')
RETURNING customer_id;

 customer_id
-------------
          58

Seeing exactly what an UPDATE changed

Confirming the post-update values

SQL

UPDATE products
SET unit_price = unit_price * 1.05
WHERE sku = 'SKU-1002'
RETURNING sku, unit_price;

 sku      | unit_price
----------+------------
 SKU-1002 |      93.45

Capturing rows before a DELETE removes them

RETURNING on a DELETE is the only opportunity to see the deleted data, since after the statement finishes it no longer exists anywhere in the table.

Archiving a deleted row's data in the same statement

SQL

DELETE FROM orders
WHERE status = 'cancelled' AND order_date < now() - INTERVAL '1 year'
RETURNING order_id, customer_id, order_date;

RETURNING * vs. specific columns

RETURNING * returns every column of the affected row, exactly as it ended up after the statement. Naming specific columns instead — RETURNING customer_id, email — keeps the result focused on what the caller actually needs, the same tradeoff as SELECT * versus naming columns explicitly.

Form	Returns
RETURNING *	Every column of each affected row
RETURNING col1, col2	Only the named columns
RETURNING col AS alias	A named column under a chosen alias, same as in SELECT
RETURNING expr	Any expression computed from the affected row, e.g. RETURNING price * quantity

RETURNING reflects the final state of the row for UPDATE

For UPDATE, RETURNING reports the row's values after the update has been applied, not before. If both the old and new values are needed, the old values must be captured separately beforehand (for example, in application code, or by reading them before the UPDATE runs).

RETURNING appends to INSERT, UPDATE, or DELETE to get back the affected row(s) without a separate SELECT.
Most commonly used to retrieve an auto-generated id right after INSERT.
On UPDATE it reflects the post-update values; on DELETE it is the only chance to see the removed data.
RETURNING * returns every column; naming columns keeps the result focused.
This exact capability is uncommon outside PostgreSQL and a handful of other advanced SQL engines.