Postgres by Example: RETURNING

RETURNING is a PostgreSQL extension that turns an INSERT, UPDATE, or DELETE into a query: the statement returns the rows it just touched. This eliminates the second round trip you'd otherwise need to fetch the auto-generated id, the updated timestamp, or the values you just deleted. Most professional codebases lean on RETURNING heavily.

What you'll learn:

• RETURNING with INSERT, UPDATE, DELETE
• Returning specific columns vs RETURNING *
• Capturing generated values (identity, defaults) in one call
• Using RETURNING inside a CTE (WITH ... AS (...)) to chain operations
• Why it almost always replaces "INSERT then SELECT last_insert_id()"

-- Setup
DROP TABLE IF EXISTS logs_example;
CREATE TABLE logs_example (
  id   bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  msg  text NOT NULL,
  at   timestamptz NOT NULL DEFAULT current_timestamp
);

-- INSERT ... RETURNING: get the generated id and timestamp in one round trip
INSERT INTO logs_example (msg) VALUES ('first') RETURNING id, msg, at;
INSERT INTO logs_example (msg) VALUES ('second'), ('third') RETURNING id;

-- UPDATE ... RETURNING: see the new values of the modified rows
UPDATE logs_example
SET    msg = msg || ' (updated)'
WHERE  id = 1
RETURNING id, msg;

-- DELETE ... RETURNING: see what was removed
DELETE FROM logs_example
WHERE  id = 3
RETURNING *;

-- Chaining: insert into one table, then route the new ids into another
DROP TABLE IF EXISTS log_archive;
CREATE TABLE log_archive (id bigint PRIMARY KEY, msg text, archived_at timestamptz DEFAULT current_timestamp);

WITH moved AS (
  DELETE FROM logs_example
  WHERE id <= 2
  RETURNING id, msg
)
INSERT INTO log_archive (id, msg)
SELECT id, msg FROM moved
RETURNING id;

RETURNING accepts the same expressions you would put in a SELECT list — column references, expressions, function calls, even *. For an INSERT, the columns reflect the post-insert values, including defaults and generated identities. For an UPDATE, they reflect the new values; use OLD.col only inside triggers, not in RETURNING. For a DELETE, they reflect the row as it was.

The chaining pattern (WITH del AS (DELETE ... RETURNING) INSERT INTO archive SELECT * FROM del) is one of PostgreSQL's superpowers. The two statements run as a single, atomic operation in one round trip — much faster and simpler than doing it from application code.

To run:

$ psql -f source/returning.sql postgres
DROP TABLE
CREATE TABLE
 id |  msg  |              at
----+-------+-------------------------------
  1 | first | 2024-xx-xx xx:xx:xx.xxxxxx+00
(1 row)
 id
----
  2
  3
(2 rows)

 id |       msg
----+-----------------
  1 | first (updated)
(1 row)
 ...

Common pitfalls:

• Forgetting RETURNING on an INSERT and then issuing a SELECT max(id) FROM t; — that is a race condition. Always use RETURNING id for the new row's id.
• Multi-row INSERT ... RETURNING returns rows in the order they were inserted, but only because of how the executor processes them. If you need a guaranteed mapping, include a stable key in the VALUES and the RETURNING.
• RETURNING inside INSERT ... ON CONFLICT DO NOTHING does not return rows that were skipped due to conflict. Use DO UPDATE (covered in the next lesson) to make sure something is returned.
• Drivers sometimes expose INSERT ... RETURNING as a query (with results) rather than as a statement (with row count). Check the language API.

Tip: INSERT INTO t (...) VALUES (...) RETURNING id is the canonical pattern for "create a row and tell me its id." It works with bigint GENERATED AS IDENTITY, serial, UUID defaults, or anything else the database supplies. Use it everywhere.

Try it: Insert three rows with one statement and capture all three ids via RETURNING id. Then run UPDATE logs_example SET msg = msg WHERE id > 0 RETURNING id, msg; to see every row that was touched (a "no-op" update is still a row touch). Then use the CTE chaining pattern to move rows from logs_example to log_archive.

Source: returning.sql

Next: UPSERT (ON CONFLICT)

Home: Postgres by Example