Why UPDATE with RETURNING clause in PostgreSQL? - Purpose & Use Cases

The Big Idea

What if you could update your data and instantly see the results without extra searching?

The Scenario

Imagine you have a big list of customer orders in a spreadsheet. You want to change the status of some orders and then see exactly which ones changed and what their new status is. Doing this by hand means updating each order one by one and then searching again to find the updated rows.

The Problem

Manually updating and then searching again is slow and easy to mess up. You might forget which orders you changed or accidentally check the wrong rows. It wastes time and can cause mistakes, especially when working with many records.

The Solution

The UPDATE with RETURNING clause lets you update rows and immediately get back the changed data in one step. This means you don't have to run a separate search after updating. It saves time and reduces errors by showing exactly what was updated right away.

Before vs After

✗ Before

UPDATE orders SET status = 'shipped' WHERE order_date < '2024-01-01';
SELECT * FROM orders WHERE status = 'shipped' AND order_date < '2024-01-01';

✓ After

UPDATE orders SET status = 'shipped' WHERE order_date < '2024-01-01' RETURNING order_id, status;

What It Enables

This lets you instantly see which records changed and their new values, making your updates clear and efficient.

Real Life Example

A store manager updates all pending orders before a holiday and immediately gets a list of those orders now marked as shipped, ready to inform customers.

Key Takeaways

Updating and retrieving changed data happens in one step.

Reduces errors by showing exactly what was updated.

Saves time by avoiding extra queries after updates.