Why Returning modified rows with RETURNING in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could update data and see exactly what changed in just one simple step?

The Scenario

Imagine you update several records in a big table and then want to know exactly which rows changed and how. You try to do this by running an update, then running a separate select query to find the updated rows.

The Problem

This manual way is slow because you run two queries instead of one. It can also cause mistakes if the data changes between your update and select. You might miss some changes or get wrong results.

The Solution

The RETURNING clause lets you update rows and get the changed rows back immediately in one step. This saves time and avoids errors because you see exactly what was updated right away.

Before vs After

✗ Before

UPDATE users SET active = false WHERE last_login < '2023-01-01';
SELECT * FROM users WHERE active = false AND last_login < '2023-01-01';

✓ After

UPDATE users SET active = false WHERE last_login < '2023-01-01' RETURNING *;

What It Enables

You can instantly see the exact rows you modified, making your database work faster and more reliable.

Real Life Example

A company disables old user accounts and immediately gets a list of those accounts to notify the users by email.

Key Takeaways

Manual update then select is slow and error-prone.

RETURNING gives updated rows instantly in one query.

This makes database updates faster and safer.