Postgres RETURNING projects rows affected by INSERT, UPDATE, or DELETE—eliminating extra round trips to fetch generated keys or audit changes.
INSERT … RETURNING
INSERT INTO customers (email)
VALUES ('ada@example.com')
RETURNING id, email, created_at;Practice: Run in psql or DB Fiddle (PostgreSQL). Use your practice database from the workflow lesson.
UPDATE and DELETE
UPDATE customers
SET email = 'ada+new@example.com'
WHERE id = 1
RETURNING id, email;
DELETE FROM customers
WHERE id = 999
RETURNING id;API and ORM pattern
REST handlers often return the created row body from RETURNING instead of a second SELECT. Django querysets can use .values() after create or raw SQL with RETURNING for bulk inserts.
Important interview questions and answers
- Q: RETURNING with multiple rows?
A: Works for any DML affecting rows—returns all matching rows. - Q: MySQL equivalent?
A: MySQL lacks RETURNING in older versions; Postgres has had it for years—portable apps may still SELECT separately.
Self-check
- How do you get a new SERIAL id after INSERT?
- Can UPDATE use RETURNING?
Tip: Use RETURNING in APIs to avoid a second round trip for new primary keys.
Interview prep
- INSERT RETURNING?
Projects inserted row including generated keys.
- DELETE RETURNING?
Returns deleted rows for auditing or APIs.