PostgreSQL RETURNING Clause
intermediatepostgresql
2 min read
What is RETURNING?
RETURNING is a PostgreSQL extension that returns data from rows affected by INSERT, UPDATE, or DELETE statements. It eliminates the need for a separate SELECT query.
Syntax
INSERT INTO table_name (columns) VALUES (values) RETURNING *;
UPDATE table_name SET column = value WHERE condition RETURNING column1, column2;
DELETE FROM table_name WHERE condition RETURNING *;When to Use
- Getting the auto-generated ID after an insert
- Confirming which rows were updated
- Logging deleted records
- Reducing round trips between application and database
Key Points
- Any DML Statement — Works with INSERT, UPDATE, and DELETE.
- Column Selection — Return specific columns or
*for all. - Expressions — You can use expressions:
RETURNING id, name || ' updated' AS msg. - One Round Trip — Combines the mutation and the select into a single statement.
- CTE Usage — Combine with CTEs:
WITH deleted AS (DELETE FROM t RETURNING *) SELECT COUNT(*) FROM deleted;. - Not Standard SQL — RETURNING is a PostgreSQL extension.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
PostgreSQL RETURNING Clause Challenge
Write a query that solve this task: insert a student and immediately get the generated ID.
Expected result
Returns the auto-generated id for the newly inserted student.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What is RETURNING?
Initializing database...Each run starts from fresh sample data.
More Examples
Return updated rows
Update prices and see the new values.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
Does RETURNING work with UPDATE?
Yes. RETURNING works with INSERT, UPDATE, and DELETE. It returns the affected rows after the operation.
Is RETURNING available in MySQL?
No. RETURNING is PostgreSQL-specific. In MySQL, use LAST_INSERT_ID() for inserts.