Menu

PostgreSQL RETURNING for INSERT, UPDATE and DELETE

RETURNING makes INSERT, UPDATE, DELETE and MERGE hand back the rows they changed: generated ids, new values, deleted rows, and which MERGE action ran.

This page includes runnable editors. Edit, run, and see output instantly.

RETURNING makes a data-changing statement return rows, like a SELECT. Add it to the end of an INSERT, UPDATE, DELETE or (since PostgreSQL 17) MERGE, followed by the columns or expressions you want: INSERT INTO users (email) VALUES ('ada@example.com') RETURNING id;.

Both new rows come back with the ids PostgreSQL generated and the plan filled in by its default, with no second query.

Get the id of an inserted row

RETURNING id is the standard way to get a generated key. It reports the row as it was stored, so it works for identity columns, serial, UUID defaults and values set by a BEFORE trigger. A multi-row VALUES or INSERT ... SELECT returns one row per inserted row.

Drivers read it like any query result: cursor.fetchone() in Python with psycopg, result.rows in Node.js with pg. RETURNING * is fine while exploring; in application code, list the columns so the result stays stable when the table changes.

See what an UPDATE changed

On an UPDATE, RETURNING shows the rows after the change, and only the rows that matched the WHERE:

Two rows come back, Book at 16.5 and Lamp at 33. The Pen was not touched, so it is not in the result.

In PostgreSQL 17, RETURNING sees only the new values. PostgreSQL 18 adds old and new (RETURNING old.price, new.price). On 17, join the table to a locked copy of itself:

Each row comes back with its price before and after: 10 and 15, 20 and 25.

Return the rows a DELETE removed

DELETE ... RETURNING gives you the rows it removed, for logging, for undo, or for a job queue where a worker deletes a job and gets its data back. Inside a WITH query, the returned rows feed the next statement, which moves rows between tables in one statement:

Tasks 1 and 3 leave tasks and arrive in done_tasks. It is one statement, so no other session sees them in both tables or in neither.

Find out which action MERGE took

Since PostgreSQL 17, MERGE accepts RETURNING, and the function merge_action() returns INSERT, UPDATE or DELETE for each row:

A-100 was updated to 7 and C-300 was inserted with 4. For an insert-or-update by key under concurrent writes, prefer an upsert with ON CONFLICT, which also supports RETURNING.

What RETURNING can contain

Anything a SELECT list can: columns, *, expressions, function calls and aliases (RETURNING price * qty AS total), except aggregates. To count or sum the returned rows, wrap the statement in a WITH query and aggregate in the outer SELECT.

Rows skipped by ON CONFLICT DO NOTHING, or by a BEFORE trigger that returned NULL, are not returned.

Frequently Asked Questions

How do I get the id of the row I just inserted in PostgreSQL?

Add RETURNING id to the INSERT. It returns the generated value for every inserted row in the same round trip, which is safer than SELECT max(id) and works for identity columns, serial and defaults such as gen_random_uuid().

Is RETURNING faster than a separate SELECT?

Usually, yes. It saves a second statement and a second round trip to the server, and it returns exactly the rows the statement touched, with no window for another session to change them in between.

Does MySQL support RETURNING?

MySQL does not have a RETURNING clause; you read LAST_INSERT_ID() after an insert instead. SQLite has supported RETURNING since version 3.35.

Coddy programming languages illustration

Learn to code with Coddy

Get started