The RETURNING clause lets INSERT, UPDATE, and DELETE statements return data about the rows they affected — most commonly the auto-generated ID after an insert, but also any columns or computed values. It saves a separate query and is a convenient PostgreSQL feature.
-- get the auto-generated id back from the insert (no separate SELECT needed)
INSERT INTO users (name, email) VALUES ('Ann', '[email protected]')
RETURNING id; -- returns the new id
-- return multiple columns or all of them
INSERT INTO users (name) VALUES ('Bob')
RETURNING id, name, created_at; -- including DB-generated values
INSERT INTO users (name) VALUES ('Carol') RETURNING *; -- the whole row
The most common use: after inserting, you usually need the generated primary key (to use in related inserts or return to the client). RETURNING id gives it back directly — no need for a separate query.
-- see what was updated
UPDATE products SET price = price * 1.1 WHERE category = 'books'
RETURNING id, name, price; -- the NEW values after the update
-- capture deleted rows (e.g. for archiving/logging)
DELETE FROM sessions WHERE expired = true
RETURNING id, user_id; -- which rows were deleted
RETURNING works on UPDATE (see the new values) and DELETE (capture what was removed — useful for archiving or confirming).
Without RETURNING:
INSERT ...; then a separate SELECT to get the id → TWO round-trips, and a potential
race (the SELECT might not reliably get YOUR row).
With RETURNING:
one statement returns the affected data atomically → fewer round-trips, no race.
-- use a CTE with RETURNING to insert and use the id in the same statement
WITH new_user AS (
INSERT INTO users (name) VALUES ('Ann') RETURNING id
)
INSERT INTO profiles (user_id, bio)
SELECT id, 'Hello' FROM new_user; -- use the returned id directly
The RETURNING clause is a convenient, frequently-used PostgreSQL feature that's valuable for efficiency and correctness when modifying data.
Its most common and important use is getting the auto-generated ID back from an INSERT — a near-universal need (you almost always want the new primary key after inserting, to use in related operations or return to the application), and RETURNING id provides it directly in one statement.
Without RETURNING, you'd need a separate SELECT to get the generated ID, which means an extra round-trip and a potential race condition (the separate SELECT might not reliably retrieve your specific row in concurrent scenarios), so RETURNING is both more efficient and more correct.
Beyond inserts, RETURNING works with UPDATE (seeing the new values after an update) and DELETE (capturing what was removed — useful for archiving, logging, or confirming deletions).
Combined with CTEs, it enables powerful patterns like inserting into related tables using the returned ID in a single statement.
Since getting generated values (especially IDs) after data modifications is a constant need, and since RETURNING provides this efficiently and atomically (avoiding extra queries and race conditions), understanding the RETURNING clause is valuable, frequently-applied PostgreSQL knowledge that makes data-modification code cleaner, more efficient, and more correct — a practical convenience that's used routinely in real applications working with Postgres, particularly for the ubiquitous need of retrieving generated IDs after inserts.