RETURNING: PostgreSQL's Secret Weapon

~10 min · queries, write

Level 0Schema Seedling

0 XP0/86 lessons0/10 achievements

0/120 XP to next level120 XP to go0% complete

Get the receipt

Most SQL databases run INSERT/UPDATE/DELETE as fire-and-forget. PostgreSQL's RETURNING turns them into operations that return data — the affected rows come back as if you'd run a SELECT against them.

Why this matters

The classic alternative is "INSERT, then SELECT to get the new ID." That's two round trips, plus a race condition between the insert and the select. RETURNING does both in one statement, atomically. Halve your latency, eliminate the race.

Combine with CTEs for atomic chains

Wrap a DELETE ... RETURNING inside a CTE and then INSERT the result somewhere else — and you have an atomic "delete from one place, copy to another" in a single statement.

Code

RETURNING with INSERT/UPDATE/DELETE·sql

INSERT INTO users (name, email)
VALUES ('Alice', 'alice@example.com')
RETURNING id, created_at;

UPDATE products SET price = price * 0.9 WHERE category = 'clearance'
RETURNING id, name, price AS new_price;

DELETE FROM sessions WHERE expires_at < now()
RETURNING id, user_id, expires_at;

Atomic delete-and-archive with CTE·sql

WITH archived AS (
  DELETE FROM orders
  WHERE status = 'cancelled'
    AND placed_at < now() - INTERVAL '1 year'
  RETURNING *
)
INSERT INTO orders_archive
SELECT * FROM archived;

External links

PostgreSQL RETURNING

Exercise

Take an existing 'insert then select to get the ID' pattern in your code and rewrite it as a single INSERT ... RETURNING. Measure the latency improvement.