RETURNING in PostgreSQL: Get IDs and Changed Rows Without a Second Round-Trip

When you insert a row and immediately want its generated id without firing a second query, the RETURNING clause is the answer. This PostgreSQL extension turns INSERT, UPDATE, and DELETE into statements that return rows — just like SELECT, but over the data you just wrote.

Why RETURNING matters

The classic case: you insert a user and need its id for related rows. Without RETURNING you run the insert, then a separate SELECT (or call currval), losing a round-trip to the database and risking a race. RETURNING hands the values straight back from the same command.

INSERT INTO users (email, name, country)
VALUES ('a@example.com', 'Ann', 'DE')
RETURNING id, created_at;

Key properties:

• Works on INSERT, UPDATE, and DELETE.
• Returns one row per affected row, not just the first.
• The list accepts any columns, expressions over them, and aliases.

RETURNING * and computed columns

When you want every column of the resulting row — including defaults and whatever triggers filled in — use RETURNING *. That is especially handy when some fields are populated on the database side.

INSERT INTO orders (user_id, amount, status)
VALUES (42, 99.50, 'pending')
RETURNING *;

The RETURNING list can hold expressions, not just bare columns:

UPDATE orders
SET amount = amount * 1.10
WHERE status = 'pending'
RETURNING id, amount AS new_amount, round(amount / 1.10, 2) AS old_amount;

DELETE can hand back what it removed too — a ready-made audit of exactly what disappeared:

DELETE FROM orders
WHERE status = 'cancelled'
RETURNING id, user_id, amount;

RETURNING inside a CTE

The most powerful use is feeding the RETURNING result into a common table expression (CTE) and using it in the next step. That builds an atomic "insert the parent, grab its id, insert the children" chain in a single query.

WITH new_user AS (
  INSERT INTO users (email, name, country)
  VALUES ('team@example.com', 'Team', 'ES')
  RETURNING id
)
INSERT INTO orders (user_id, amount, status)
SELECT id, 0, 'pending'
FROM new_user
RETURNING id AS order_id, user_id;

The same trick solves "update and log the history": capture old values via a subquery and store them next to the new ones.

WITH bumped AS (
  UPDATE employees
  SET salary = salary * 1.05
  WHERE dept = 'eng'
  RETURNING id, salary AS new_salary
)
SELECT id, new_salary FROM bumped ORDER BY id;

Useful patterns:

• Moving rows: DELETE ... RETURNING * inside a CTE, then INSERT the result into an archive table.
• Returning both an id and a count of affected rows via count(*) on top of the CTE.
• Capturing both old and new values of a column in one pass.

Gotcha: RETURNING sees the new row, and MySQL lacks it

A few traps to watch:

• In UPDATE ... RETURNING, the columns are already the new values. To capture the old one you need a subquery or CTE against the original table; the statement alone will not show it.
• Row order in RETURNING is not guaranteed. If you need ordering, wrap it in a CTE and sort with an outer SELECT ... ORDER BY.
• RETURNING hands back only the rows actually written. With INSERT ... ON CONFLICT DO NOTHING, a row skipped on conflict is not returned at all.
• MySQL does not support the RETURNING clause (MariaDB 10.5+ does, for INSERT and DELETE). In MySQL, read the auto-increment after the insert via LAST_INSERT_ID():

-- MySQL: no RETURNING, read the generated id separately
INSERT INTO users (email, name, country)
VALUES ('a@example.com', 'Ann', 'DE');
SELECT LAST_INSERT_ID();

ClickHouse has no RETURNING either: inserts there are asynchronous and batched, so generate keys on the application side. Bottom line: on PostgreSQL, RETURNING saves a round-trip and closes races; on MySQL, keep LAST_INSERT_ID() handy.