RETURNING clause
RETURNING gives you the rows that were inserted, updated or deleted, without a second query.
In this page:
Syntax
INSERT INTO table_name (column) VALUES (value) RETURNING columns;
UPDATE table_name SET column = value WHERE condition RETURNING columns;RETURNING clause
Add RETURNING columns to INSERT, UPDATE or DELETE and PostgreSQL returns those columns for the affected rows. It is perfect for fetching generated ids (RETURNING id) or the new values after an update.
RETURNING * returns whole rows. This is a PostgreSQL feature (SQLite only supports it in versions 3.35 and later).
Note:
- INSERT ...
- RETURNING id is the standard way to get a new id in PostgreSQL.
Example: RETURNING clause
shop=# INSERT INTO users (name, email) VALUES ('Ada', '[email protected]') RETURNING id, created_at;
id | created_at
----+-------------------------------
1 | 2024-03-15 10:30:11.123456+00
(1 row)
INSERT 0 1
shop=# UPDATE users SET name = 'Ada L.' WHERE id = 1 RETURNING *;
id | name | email | created_at
----+--------+-----------------+-------------------------------
1 | Ada L. | [email protected] | 2024-03-15 10:30:11.123456+00
UPDATE 1
shop=# DELETE FROM users WHERE id = 1 RETURNING name;
name
--------
Ada L.
DELETE 1⚠️ Run this in your own terminal or Node.js environment.
Related Topics
Common Mistakes
- Running a second SELECT to get the new id
- Forgetting RETURNING returns rows to the client
- Expecting it in every database
Chapter Summary
- RETURNING returns affected rows
- Works on INSERT, UPDATE and DELETE
- RETURNING id gets generated ids
- RETURNING * returns whole rows
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: