SQL / INSERTING, UPDATING, AND DELETING ROWS
Returning changed rows with RETURNING
Use RETURNING on INSERT, UPDATE and DELETE to read back the rows a write actually touched, including generated ids, without running a second query.
What you will learn
- Append RETURNING to INSERT, UPDATE or DELETE to read back the rows it wrote
- Capture sequence-generated ids and DEFAULT values without a follow-up SELECT
- Feed a DELETE ... RETURNING into an INSERT with a data-modifying CTE
- Recover pre-UPDATE values by joining a snapshot CTE in the FROM clause
Understanding Returning changed rows with RETURNING
RETURNING is a clause you attach to INSERT, UPDATE or DELETE, and it takes the same kind of list a SELECT does: column names, expressions, aliases, or *. The statement still writes rows, but it now also produces a result set containing exactly one row per row it wrote. That is the difference between knowing that something happened and knowing what happened: the command tag UPDATE 3 says three rows changed, while RETURNING says which three and what they now hold.
The values you most often need are ones only the server can know: an id from a sequence or identity column, a created_at filled in by DEFAULT now(), a generated column, or the result of arithmetic like qty = qty - 1. Fetching them with a second SELECT costs an extra round trip and runs under a different snapshot, so rows may have moved on in between. Re-running the original WHERE clause is not a reliable way to find the rows you touched either, because the UPDATE may have changed the very columns that WHERE tested.
The mental model is that the writing statement is a row source and RETURNING is the projection over it. For INSERT and UPDATE the projected row is the version now stored, after defaults and BEFORE triggers; for DELETE it is the row as it looked immediately before it disappeared, which is your only chance to read it. Because it is just a row source, PostgreSQL lets you wrap the whole statement in a WITH clause and select from it, so one statement can archive and delete at the same time. It is not a full SELECT though: the RETURNING list accepts no ORDER BY, LIMIT or GROUP BY of its own, and the row order is unspecified.
CREATE TABLE orders (
id serial PRIMARY KEY,
customer text NOT NULL,
amount numeric(8,2) NOT NULL,
status text NOT NULL DEFAULT 'pending'
);
INSERT INTO orders (customer, amount)
VALUES ('Ada', 120.00),
('Grace', 75.50)
RETURNING id, customer, status;
UPDATE orders
SET status = 'shipped'
WHERE amount > 100
RETURNING id, customer, status;
DELETE FROM orders
WHERE customer = 'Grace'
RETURNING *;A write with RETURNING is also a query: it emits one row per row it actually changed, valued as stored at that moment.
Worked examples
Move rows between tables in one statement
A DELETE ... RETURNING inside a CTE hands the removed rows to an INSERT, so archiving and deleting happen together.
CREATE TABLE tasks (id int PRIMARY KEY, title text, done boolean);
CREATE TABLE tasks_archive (id int, title text);
INSERT INTO tasks VALUES (1, 'write tests', true),
(2, 'fix bug', false),
(3, 'ship', true);
WITH moved AS (
DELETE FROM tasks
WHERE done
RETURNING id, title
)
INSERT INTO tasks_archive (id, title)
SELECT id, title FROM moved
RETURNING id, title;
SELECT * FROM tasks;Example explained
Line 1RETURNING id, title inside the CTE keeps the deleted rows alive as a result set instead of discarding them.
Line 2The outer INSERT reads moved like a table, so the rows are written to tasks_archive before they are lost.
Line 3The outer RETURNING reports what the INSERT stored, which is why no extra SELECT on tasks_archive is needed.
Line 4A data-modifying CTE runs once and completely, so the DELETE cannot fire again per referenced row.
See the old and the new value in one row
RETURNING shows the post-update row, so the previous value has to come from a snapshot joined into the UPDATE.
CREATE TABLE accounts (
id int PRIMARY KEY,
holder text,
balance numeric(10,2)
);
INSERT INTO accounts VALUES (1, 'Ada', 500.00), (2, 'Grace', 300.00);
WITH prev AS (
SELECT id, balance FROM accounts WHERE id = 1
)
UPDATE accounts a
SET balance = a.balance - 50
FROM prev
WHERE prev.id = a.id
RETURNING a.id, prev.balance AS old_balance, a.balance AS new_balance;Example explained
Line 1prev reads balance under the statement's snapshot, so it still holds 500.00 while the UPDATE writes 450.00.
Line 2FROM prev joins on the primary key, which keeps the join at one row and avoids updating a row twice.
Line 3RETURNING may name columns from the FROM list, not only from the target table, so both values fit one output row.
Line 4a.balance in RETURNING is the stored result, not the expression a.balance - 50 re-evaluated.
Capture the new id inside PL/pgSQL
In procedural code RETURNING can assign straight into a variable with INTO, avoiding any lookup of the sequence.
CREATE TABLE signups (
id serial PRIMARY KEY,
email text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now()
);
DO $$
DECLARE
new_id integer;
BEGIN
INSERT INTO signups (email)
VALUES ('ada@example.com')
RETURNING id INTO new_id;
RAISE NOTICE 'new signup id = %', new_id;
END
$$;Example explained
Line 1RETURNING id INTO new_id is PL/pgSQL syntax; in plain SQL, RETURNING only produces a result set for the client.
Line 2The id is taken from the inserted row itself, so a concurrent insert in another session cannot hand you the wrong number.
Line 3INTO expects a single row, so this form raises an error if the statement writes more than one row without STRICT-free array targets.
Important notes
RETURNING reflects defaults, generated columns and BEFORE-trigger edits, but if an AFTER trigger updates the row again, the copy you received is already stale.
Support varies: PostgreSQL and SQLite 3.35+ allow RETURNING on all three statements, MariaDB only on some, Oracle spells it RETURNING ... INTO, SQL Server uses OUTPUT, and MySQL has no equivalent (you fall back to LAST_INSERT_ID()).
Common mistakes
Reading UPDATE ... RETURNING as the values before the change: the row comes back as stored, so an audit table filled this way records the new price twice and the original is gone.
Writing RETURNING id ORDER BY id or adding LIMIT after the list: that is a syntax error, since RETURNING has no clauses of its own; wrap the statement in a CTE and order the outer SELECT.
Assuming INSERT ... ON CONFLICT DO NOTHING RETURNING id always yields a row: on a conflict nothing is inserted and nothing is returned, so code that reads the first row of the result crashes.
Try it yourself
Change, predict, then run
Create tickets(id serial PRIMARY KEY, title text, status text DEFAULT 'open'), insert three tickets with one INSERT that returns their ids, then run DELETE FROM tickets WHERE title LIKE 'spam%' RETURNING * and check that the returned rows are exactly the ones you meant to remove.
Open the SQL workspaceCheck your understanding
Why is INSERT INTO users (email) VALUES ('a@b.c') RETURNING id more reliable than running SELECT max(id) FROM users right after the insert?
- RETURNING reports the id of the row this statement inserted, while max(id) can return an id another session committed in the meantime.
- max(id) is slower because it has to scan the table, so under load it can time out before returning a value.
- RETURNING locks the table for the rest of the transaction, so no other session can insert a competing id.
- Rows written by other sessions stay invisible until they commit, so max(id) returns NULL inside a transaction.
Show answer
RETURNING is evaluated over the rows the INSERT itself wrote, so the id belongs to your row by construction. max(id) is a separate query over whatever is visible when it runs, and any concurrent committed insert with a higher id makes it wrong. The speed argument is tempting but it is about performance, not correctness: even an instant index-based max(id) can still report someone else's row.