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
Mafi yawan amfani: bayan saka, kana buƙatar generated primary key (don yi amfani a sake insert ko mayar wa client). RETURNING id yana ba da shi kaɗai — babu buƙatar wata bugun gida.
-- 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 yana aiki a UPDATE (duba sabbin ƙiyaye) da DELETE (kulli abin da aka cire — aiki ne mai amfani don ajiya ko tabbatarwa).
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.