UPDATE with RETURNING clause in PostgreSQL - Deep Dive

Overview - UPDATE with RETURNING clause

What is it?

The UPDATE statement changes existing data in a table. The RETURNING clause lets you get back the rows that were changed, showing their new or old values immediately. This helps you see what was updated without running a separate query. It works like a live receipt for your changes.

Why it matters

Without RETURNING, after updating data, you must run another query to see what changed, which wastes time and resources. RETURNING saves effort and reduces errors by giving instant feedback on updates. This is especially useful in apps where you need to confirm or use updated data right away.

Where it fits

You should know basic SQL commands like SELECT and UPDATE before learning this. After mastering RETURNING, you can explore advanced data manipulation techniques and learn how to use it with other commands like INSERT or DELETE that also support RETURNING.

Mental Model

Core Idea

UPDATE with RETURNING lets you change data and immediately see the updated rows in one step.

Think of it like...

It's like editing a document and instantly seeing a summary of what you changed, without flipping back and forth between pages.

┌───────────────┐
│   UPDATE      │
│  (change rows)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│  RETURNING    │
│ (show changed │
│   rows now)   │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Basics of UPDATE statement

Concept: Learn how UPDATE changes data in a table.

UPDATE table_name SET column = value WHERE condition; This changes the specified column's value for rows matching the condition.

Result

Rows matching the condition have their column updated.

Understanding how UPDATE works is essential before adding RETURNING to see changes.

2

Foundation

Understanding SELECT to view data

Concept: Learn how SELECT retrieves data from tables.

SELECT * FROM table_name WHERE condition; This shows rows matching the condition without changing anything.

Result

You see the current data in the table for those rows.

Knowing SELECT helps you understand why RETURNING is useful to get updated data immediately.

3

Intermediate

Adding RETURNING to UPDATE

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

Concept: RETURNING returns the updated rows after the change, showing new values by default.

UPDATE table_name SET column = value WHERE condition RETURNING *; This updates rows and immediately returns all columns of those rows after update.

Result

You get a result set showing the updated rows with their new values.

RETURNING combines update and retrieval, saving time and ensuring you see exactly what changed.

4

Intermediate

Selecting specific columns with RETURNING

🤔Before reading on: can RETURNING return only some columns or must it return all? Commit to your answer.

Concept: RETURNING can return only the columns you specify, not just all columns.

UPDATE table_name SET column = value WHERE condition RETURNING id, column; This returns only the id and updated column values of changed rows.

Result

You get a smaller result set with just the columns you asked for.

Choosing specific columns reduces data sent back, improving efficiency and clarity.

5

Intermediate

Using RETURNING with expressions

Concept: RETURNING can return expressions or calculations based on updated data.

UPDATE accounts SET balance = balance - 100 WHERE id = 1 RETURNING id, balance, balance * 0.1 AS fee; This returns id, new balance, and a calculated fee for each updated row.

Result

You get updated rows with extra calculated info in the result.

RETURNING lets you do more than just show columns; you can compute useful values on the fly.

6

Advanced

RETURNING with CTEs for complex updates

🤔Before reading on: do you think RETURNING can be combined with CTEs (WITH clauses) to chain updates? Commit to your answer.

Concept: You can use RETURNING inside Common Table Expressions (CTEs) to chain updates and use updated data immediately.

WITH updated AS ( UPDATE table_name SET column = value WHERE condition RETURNING * ) SELECT * FROM updated WHERE some_other_condition; This updates rows, returns them, and then filters or uses them further.

Result

You get a filtered or processed set of updated rows in one query.

Combining RETURNING with CTEs enables powerful, readable multi-step data changes in one command.

7

Expert

Performance and locking behavior with RETURNING

🤔Before reading on: does adding RETURNING affect how UPDATE locks rows or its speed? Commit to your answer.

Concept: RETURNING does not change locking but may slightly affect performance due to returning data; understanding internals helps optimize queries.

When UPDATE runs, it locks rows to prevent conflicts. RETURNING just fetches the changed rows' data after update. It doesn't add extra locks but can increase query time if returning many columns or rows. Use RETURNING wisely to balance feedback and performance.

Result

You get updated rows returned without extra locking but with some overhead.

Knowing how RETURNING interacts with locking and performance helps write efficient, safe updates in production.

Under the Hood

When you run UPDATE with RETURNING, PostgreSQL first locks the rows matching the condition to prevent other changes. It then applies the updates to those rows in memory. After updating, it collects the requested columns or expressions for those rows and sends them back as a result set. This happens in a single transaction, so the returned data is consistent and reflects the exact changes made.

Why designed this way?

RETURNING was designed to avoid the need for a separate SELECT after UPDATE, which could cause race conditions or extra load. By returning updated rows immediately, it ensures atomicity and efficiency. Alternatives like running two queries were slower and risked inconsistent data if other changes happened between queries.

┌───────────────┐
│   Client      │
└──────┬────────┘
       │ sends UPDATE with RETURNING
       ▼
┌───────────────┐
│ PostgreSQL    │
│  Executor     │
│ ┌───────────┐ │
│ │ Lock Rows │ │
│ └────┬──────┘ │
│      │        │
│ ┌────▼──────┐ │
│ │ Update    │ │
│ │ Rows      │ │
│ └────┬──────┘ │
│      │        │
│ ┌────▼──────┐ │
│ │ Collect   │ │
│ │ RETURNING │ │
│ └────┬──────┘ │
│      │        │
└──────▼────────┘
       │ sends updated rows
       ▼
┌───────────────┐
│   Client      │
└───────────────┘

Myth Busters - 4 Common Misconceptions

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

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

Reality: RETURNING returns the new values after the update is applied.

Why it matters: If you expect old values but get new ones, you might misinterpret data or miss tracking changes correctly.

Quick: Can RETURNING be used with any SQL command like SELECT or only with data-changing commands? Commit to your answer.

Common Belief: RETURNING can be used with SELECT to get data in a special way.

Reality: RETURNING only works with data-changing commands like INSERT, UPDATE, and DELETE, not with SELECT.

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

Quick: Does using RETURNING add extra locks or change transaction behavior? Commit to your answer.

Common Belief: RETURNING adds extra locks and can block other queries longer.

Reality: RETURNING does not add extra locks beyond what UPDATE already uses; it only returns data after the update.

Why it matters: Misunderstanding locking can lead to unnecessary query rewriting or avoiding RETURNING when it is safe and useful.

Quick: Does RETURNING always return all columns by default? Commit to your answer.

Common Belief: RETURNING always returns all columns of the updated rows.

Reality: RETURNING returns only the columns you specify; using * returns all columns, but you can choose fewer.

Why it matters: Expecting all columns when only some are returned can cause bugs or missing data in your application.

Expert Zone

1

RETURNING can be combined with writable CTEs to chain multiple updates and use intermediate results without temporary tables.

2

Using RETURNING with large row sets or many columns can impact performance; selecting only needed columns is a subtle but important optimization.

3

In concurrent environments, RETURNING guarantees you see the exact updated data atomically, preventing race conditions common with separate SELECT queries.

When NOT to use

Avoid RETURNING when updating millions of rows without filtering, as returning huge result sets can overwhelm clients and slow queries. Instead, use batch updates or log changes separately. Also, if you only need to confirm success without data, RETURNING is unnecessary.

Production Patterns

In real systems, RETURNING is used to update and fetch new IDs, timestamps, or computed columns in one step. It's common in APIs to update user info and return the fresh profile immediately. Also used in financial apps to adjust balances and get new totals atomically.

Connections

Atomic Transactions

RETURNING builds on atomic transactions by providing immediate feedback within the same transaction.

Understanding atomic transactions helps grasp why RETURNING guarantees consistent, race-free updated data.

Event Sourcing (Software Architecture)

RETURNING can be used to capture updated state changes instantly, similar to how event sourcing records state transitions.

Knowing event sourcing concepts clarifies how RETURNING supports reliable state tracking in databases.

Real-time Feedback Loops (Control Systems)

RETURNING provides immediate output after an input (update), like feedback loops in control systems that adjust based on current state.

Seeing RETURNING as a feedback mechanism helps appreciate its role in responsive, interactive applications.

Common Pitfalls

#1 Expecting RETURNING to show old values before update.

Wrong approach:

UPDATE users SET status = 'active' WHERE id = 1 RETURNING status;
-- Expects 'inactive' but gets 'active'

Correct approach:

UPDATE users SET status = 'active' WHERE id = 1 RETURNING status;
-- Correctly returns 'active' after update

Root cause:

Misunderstanding that RETURNING shows post-update data, not pre-update snapshots.

#2 Using RETURNING without specifying columns and expecting no performance impact.

Wrong approach:

UPDATE orders SET shipped = true RETURNING *;
-- Returns all columns even if not needed

Correct approach:

UPDATE orders SET shipped = true RETURNING id, shipped;
-- Returns only needed columns, improving efficiency

Root cause:

Not realizing that returning all columns can slow queries and increase network load.

#3 Trying to use RETURNING with SELECT statement.

Wrong approach:

SELECT * FROM users RETURNING id;
-- Syntax error

Correct approach:

SELECT * FROM users;
-- Use SELECT alone; RETURNING only works with UPDATE/INSERT/DELETE

Root cause:

Confusing RETURNING as a general data retrieval clause instead of a data-changing command feature.

Key Takeaways

UPDATE with RETURNING lets you change data and immediately get back the updated rows in one step.

RETURNING returns the new values after the update, not the old ones before the change.

You can specify which columns or expressions RETURNING sends back to optimize performance and clarity.

Combining RETURNING with CTEs enables powerful multi-step data transformations in a single query.

Understanding RETURNING's interaction with locking and transactions helps write safe, efficient updates.