Returning modified rows with RETURNING in PostgreSQL - Deep Dive

Overview - Returning modified rows with RETURNING

What is it?

In PostgreSQL, the RETURNING clause lets you get back the rows that were changed by an INSERT, UPDATE, or DELETE command. Instead of running a separate query to see what changed, RETURNING gives you the new or old data right away. This helps you work faster and with less code.

Why it matters

Without RETURNING, you would need extra queries to find out what data was added, changed, or removed. This wastes time and can cause mistakes if the data changes between queries. RETURNING solves this by giving you the exact rows affected immediately, making your work more reliable and efficient.

Where it fits

Before learning RETURNING, you should understand basic SQL commands like INSERT, UPDATE, and DELETE. After mastering RETURNING, you can explore advanced SQL features like triggers, stored procedures, and transaction control to build more powerful database applications.

Mental Model

Core Idea

RETURNING lets you capture and see the exact rows changed by your data modification commands instantly.

Think of it like...

It's like sending a letter and getting a signed receipt back immediately, proving what you sent and when.

┌───────────────┐
│ INSERT/UPDATE/ │
│ DELETE command │
└───────┬───────┘
        │
        ▼
┌─────────────────────────────┐
│ RETURNING clause captures   │
│ the affected rows instantly │
└─────────────┬───────────────┘
              │
              ▼
      ┌─────────────┐
      │ Result set  │
      │ of changed  │
      │ rows shown  │
      └─────────────┘

Build-Up - 8 Steps

1

Foundation

Basic data modification commands

Concept: Learn the core SQL commands that change data: INSERT, UPDATE, DELETE.

INSERT adds new rows to a table. UPDATE changes existing rows based on a condition. DELETE removes rows that match a condition. These commands change data but do not show you what changed by default.

Result

Data in the table changes, but you see no immediate feedback about which rows were affected.

Understanding these commands is essential because RETURNING builds on them to give you immediate feedback on changes.

2

Foundation

Why feedback on changes matters

Concept: Recognize the need to know exactly what data was changed after a command runs.

Imagine you update a customer's address but want to confirm the new address saved correctly. Without RETURNING, you must run a separate SELECT query. This can cause errors if data changes between queries or slow down your app.

Result

You realize that separate queries add complexity and risk mistakes.

Knowing why feedback matters prepares you to appreciate how RETURNING simplifies and secures your data workflows.

3

Intermediate

Using RETURNING with INSERT

🤔Before reading on: do you think RETURNING can show the new row's ID after insertion? Commit to your answer.

Concept: Learn how RETURNING returns the inserted row's data, including auto-generated columns.

When you insert a row, you can add RETURNING * to get back the full row just added. For example: INSERT INTO users(name) VALUES('Anna') RETURNING *; This returns the new user's ID and name immediately.

Result

The query returns the inserted row with all columns, including generated IDs.

Understanding RETURNING with INSERT helps you get new row details without extra queries, saving time and avoiding errors.

4

Intermediate

Using RETURNING with UPDATE

🤔Before reading on: do you think RETURNING shows old or new values after UPDATE? Commit to your answer.

Concept: RETURNING returns the updated rows with their new values after an UPDATE command.

You can update rows and get the new data back immediately: UPDATE users SET name = 'Annabelle' WHERE id = 1 RETURNING *; This returns the updated row with the new name.

Result

The query returns the rows after they were changed, showing new values.

Knowing RETURNING returns updated rows lets you confirm changes instantly and use the new data right away.

5

Intermediate

Using RETURNING with DELETE

🤔Before reading on: do you think RETURNING can show deleted rows? Commit to your answer.

Concept: RETURNING can return the rows that were deleted, showing their data before removal.

When deleting rows, you can get the deleted data back: DELETE FROM users WHERE id = 2 RETURNING *; This returns the full row that was removed.

Result

The query returns the deleted rows, letting you see what was removed.

Understanding RETURNING with DELETE helps you keep track of removed data without extra queries.

6

Advanced

RETURNING with expressions and specific columns

🤔Before reading on: can RETURNING return only some columns or computed values? Commit to your answer.

Concept: RETURNING can return specific columns or expressions, not just full rows.

You can specify columns or calculations: UPDATE users SET points = points + 10 WHERE id = 3 RETURNING id, points, points * 2 AS double_points; This returns only selected columns and a computed value.

Result

The query returns a customized result set with chosen columns and expressions.

Knowing you can customize RETURNING output lets you get exactly the data you need, improving efficiency.

7

Advanced

Using RETURNING in multi-row operations

🤔Before reading on: does RETURNING return all affected rows or just one? Commit to your answer.

Concept: RETURNING returns all rows affected by the command, not just one.

If you update multiple rows, RETURNING returns them all: UPDATE users SET active = false WHERE last_login < '2023-01-01' RETURNING id, active; This returns every row changed by the update.

Result

The query returns a result set with all affected rows, allowing batch feedback.

Understanding multi-row RETURNING helps you handle bulk changes and their results cleanly.

8

Expert

RETURNING in complex queries and performance

🤔Before reading on: does RETURNING affect performance or transaction behavior? Commit to your answer.

Concept: RETURNING integrates with transactions and can impact performance; understanding this helps optimize usage.

RETURNING runs inside the same transaction as the data change, so it sees consistent data. However, returning large result sets can slow queries. Also, RETURNING can be used with CTEs (WITH clauses) to chain commands efficiently: WITH updated AS ( UPDATE users SET points = points + 5 WHERE active = true RETURNING id, points ) SELECT * FROM updated; This returns updated rows and lets you use them immediately in further queries.

Result

You get consistent, immediate results and can chain commands, but must watch for performance on large data.

Knowing RETURNING's transactional behavior and integration with CTEs unlocks powerful, efficient query patterns.

Under the Hood

When a data modification command runs with RETURNING, PostgreSQL executes the change and simultaneously collects the affected rows into a temporary result set. This happens inside the same transaction, ensuring data consistency. The server then sends this result set back to the client as part of the query response, avoiding the need for a separate SELECT query.

Why designed this way?

RETURNING was designed to reduce round-trips between client and server, improving performance and reliability. Before RETURNING, applications had to run extra queries to fetch changed data, which could lead to race conditions or stale reads. By integrating result retrieval into the modification command, PostgreSQL provides atomic, efficient feedback.

┌───────────────────────────────┐
│ Client sends INSERT/UPDATE/DELETE + RETURNING │
└───────────────┬───────────────┘
                │
                ▼
      ┌─────────────────────┐
      │ PostgreSQL Server    │
      │ 1. Execute command   │
      │ 2. Collect changed   │
      │    rows into result  │
      │ 3. Send result back  │
      └─────────────┬───────┘
                    │
                    ▼
           ┌────────────────┐
           │ Client receives │
           │ changed rows   │
           └────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does RETURNING return the old values before an UPDATE or the new values after? Commit to your answer.

Common Belief: RETURNING returns the old values before the update.

Reality: RETURNING returns the new values after the update has been applied.

Why it matters: If you expect old values but get new ones, you might misinterpret data changes or fail to log correct history.

Quick: Can RETURNING be used with SELECT statements? Commit to yes or no.

Common Belief: RETURNING can be used with any SQL command, including SELECT.

Reality: RETURNING only works with INSERT, UPDATE, and DELETE commands, not SELECT.

Why it matters: Trying to use RETURNING with SELECT causes syntax errors and confusion about its purpose.

Quick: Does RETURNING always improve performance? Commit to yes or no.

Common Belief: Using RETURNING always makes queries faster because it reduces extra queries.

Reality: RETURNING reduces round-trips but can slow down queries if returning large amounts of data or complex expressions.

Why it matters: Assuming RETURNING is always faster can lead to performance issues in large-scale or complex operations.

Quick: Does RETURNING guarantee atomic visibility of changes? Commit to yes or no.

Common Belief: RETURNING results might show partial or inconsistent data during concurrent transactions.

Reality: RETURNING runs inside the same transaction, so it shows a consistent, atomic view of changes.

Why it matters: Knowing this prevents unnecessary locking or extra queries to ensure data consistency.

Expert Zone

1

RETURNING can be combined with Common Table Expressions (CTEs) to chain multiple data modifications and queries in a single statement, improving efficiency.

2

When using RETURNING with triggers, the returned rows reflect the final state after all triggers have run, which can differ from the immediate command effect.

3

Large RETURNING result sets can increase memory usage and network load, so it's important to limit returned columns or rows when possible.

When NOT to use

Avoid RETURNING when modifying huge numbers of rows if you don't need the changed data immediately, as it can degrade performance. Instead, consider running separate SELECT queries with proper filtering or using logging tables for auditing.

Production Patterns

In real-world systems, RETURNING is often used to get auto-generated IDs after inserts, confirm updates in APIs, or capture deleted rows for audit logs. It's also used in multi-step transactions with CTEs to pass changed data between steps without extra queries.

Connections

Transactions

RETURNING operates inside transactions to provide atomic, consistent results.

Understanding transactions helps you see why RETURNING results are reliable and how they fit into safe data changes.

Change Data Capture (CDC)

RETURNING provides immediate feedback on data changes, similar to how CDC tracks changes over time.

Knowing RETURNING helps grasp how databases track and expose data modifications for replication or auditing.

Event-driven programming

RETURNING lets applications react instantly to data changes, like events triggering actions.

Seeing RETURNING as a way to get immediate change events helps design responsive, efficient applications.

Common Pitfalls

#1 Expecting RETURNING to show old values after UPDATE.

Wrong approach:

UPDATE users SET name = 'Bob' WHERE id = 1 RETURNING *; -- expecting old name

Correct approach:

UPDATE users SET name = 'Bob' WHERE id = 1 RETURNING *; -- returns new name 'Bob'

Root cause:

Misunderstanding that RETURNING shows the state after the command, not before.

#2 Using RETURNING with SELECT statement.

Wrong approach:

SELECT * FROM users RETURNING *;

Correct approach:

SELECT * FROM users; -- no RETURNING clause with SELECT

Root cause:

Confusing RETURNING as a general output clause instead of one specific to data modification commands.

#3 Returning all columns and rows unnecessarily in large updates.

Wrong approach:

UPDATE orders SET status = 'shipped' RETURNING *;

Correct approach:

UPDATE orders SET status = 'shipped' RETURNING id, status;

Root cause:

Not limiting RETURNING output causes excessive data transfer and slows performance.

Key Takeaways

RETURNING lets you get the exact rows changed by INSERT, UPDATE, or DELETE immediately, saving extra queries.

It returns the new state of rows after changes, not the old values before modification.

You can customize RETURNING to return specific columns or expressions, improving efficiency.

RETURNING works inside transactions, ensuring consistent and atomic results.

Using RETURNING wisely in production improves performance and reliability but be cautious with large result sets.