ReviseAlgo Logo

Data Modification

RETURNING Clause

Obtaining written rows immediately from database write operations.

Last Updated: June 15, 2026 • 14 min read

1. Introduction

The RETURNING clause in PostgreSQL lets you retrieve rows affected by INSERT, UPDATE, or DELETE operations — all in the same query. Instead of writing a separate SELECT after every write, RETURNING gives you the modified data immediately, reducing round trips and eliminating race conditions.

2. Why It Matters

  • Eliminate extra queries: Get the auto-generated ID after INSERT without a separate SELECT — saves a full database round trip.
  • Atomic read-after-write: The returned data is guaranteed to be the exact state after the write, with no risk of concurrent modifications between write and read.
  • Audit logging: Capture old values during UPDATE/DELETE for audit trails without triggers or separate queries.
  • API response building: REST APIs often need to return the created/updated resource — RETURNING provides this directly.

3. Real-World Analogy

Imagine dropping off a package at a shipping center. Without RETURNING, you drop it off, then go to a separate counter to ask "what was my tracking number?" With RETURNING, the clerk hands you the receipt with the tracking number immediately — same transaction, no extra step.

4. How It Works

RETURNING with INSERT:

RETURNING with UPDATE:

RETURNING with DELETE:

5. Internal Architecture

6. Visual Explanation

7. Practical Example

API endpoint pattern — create user and return full record:

Soft delete with audit trail:

8. Common Mistakes

Expecting RETURNING in MySQL

RETURNING is a PostgreSQL feature (also supported by SQLite and Oracle). MySQL doesn't support it — you must use LAST_INSERT_ID() and a separate SELECT. Be aware of database-specific syntax.

Not handling empty RETURNING results

If an UPDATE or DELETE affects zero rows, RETURNING returns zero rows (not NULL). Application code must handle the empty result case to avoid null pointer errors.

Interview Insight

"How do you get the auto-generated ID after an INSERT without an extra query?" — Use RETURNING id. This is atomic, race-condition free, and eliminates a round trip. In ORMs like JPA/Hibernate, this maps to the @Generated annotation.

9. Quick Quiz

Q1: Which SQL statements support the RETURNING clause?

Answer: INSERT, UPDATE, and DELETE. It's not available for SELECT (which already returns data) or DDL statements like CREATE TABLE.

Q2: Can RETURNING include expressions and computed columns?

Answer: Yes. RETURNING supports any expression: RETURNING *, price * quantity AS total, NOW() AS processed_at. It works just like a SELECT list.

10. Scenario-Based Challenge

Challenge: Order Processing Pipeline

Design a single-query order placement that:

  1. INSERTs a new order and RETURNING the generated order_id.
  2. Uses that order_id to INSERT multiple order_items (using a CTE).
  3. UPDATEs inventory to reduce stock for each item.
  4. Returns a complete order confirmation with order_id, item count, and total price — all without a separate SELECT.

11. Debugging Exercise

Find the issue with this UPSERT + RETURNING:

Issue:

  1. DO NOTHING returns no rows on conflict: When a conflict occurs and DO NOTHING is triggered, RETURNING returns zero rows — not the existing values. If you need the current values on conflict, use DO UPDATE SET theme = EXCLUDED.theme (even if updating to the same value) so RETURNING always returns a row.

12. Interview Questions

Q1: How do you move deleted rows to an archive table efficiently?

A: Use a CTE with DELETE + RETURNING: WITH deleted AS (DELETE FROM table WHERE condition RETURNING *) INSERT INTO archive SELECT * FROM deleted. This is atomic — no rows are lost between the DELETE and the archive INSERT.

Q2: What's the performance benefit of RETURNING over a separate SELECT?

A: RETURNING eliminates a network round trip (1 query instead of 2), avoids re-scanning the table, and guarantees you see the exact post-write state. A separate SELECT could see different data if another transaction modifies the row between the write and the read.

Q3: Can you use RETURNING with ON CONFLICT DO UPDATE?

A: Yes. RETURNING works with all DML including UPSERT. It returns the final state of the row whether it was inserted or updated, making it perfect for API responses that need to return the current resource state.

13. Production Considerations

  • ORM integration: Most ORMs (Hibernate, Prisma, Sequelize) use RETURNING internally for auto-generated IDs. Verify your ORM generates RETURNING clauses for optimal performance.
  • CTE chains: Use RETURNING inside CTEs to chain multiple dependent writes in a single query (INSERT parent, then INSERT children with parent_id).
  • Trigger interaction: If a BEFORE trigger modifies a column, RETURNING shows the trigger-modified value, not the original INSERT value. This is correct but can surprise developers.
  • Batch operations: RETURNING * on bulk INSERTs returns all inserted rows. For very large batches, this can be significant network data — consider returning only essential columns.
  • Audit patterns: Combine DELETE/UPDATE RETURNING with INSERT INTO audit_log for single-query audit trails that can't miss entries.