Alright, let’s talk about RETURNING. This is the part where SQL stops being a faceless drone that just reports “UPDATE 14” and starts being a proper, helpful assistant. It’s the feature that answers the question you ask every single time you run an update or delete: “Okay, but what did I just change?”

Think about it. You just executed UPDATE users SET status = 'banned' WHERE last_login < '2020-01-01';. The database smugly informs you that it updated 47 rows. Great. Which 47? The ones with user_id 5, 78, 203…? You have no idea. You’re left in the dark, forced to run a separate SELECT to see the aftermath. It’s clunky, it’s inefficient, and it introduces a potential race condition. RETURNING eliminates this entire song and dance.

It does exactly what it says on the tin: it causes the UPDATE or DELETE statement to return the affected rows, like a SELECT statement stapled to the end of your mutation. It’s the single best way to confirm your operation did exactly what you intended.

Why You’ll Love RETURNING

First, it’s a atomic feedback loop. You get the data from the exact moment the change was made, no window for another transaction to sneak in and change things between your UPDATE and your subsequent SELECT. This is huge for reliability.

Second, it’s incredibly useful for application logic. Maybe you need the primary key of the newly inserted row, or you want to log the old values of the records you’re deleting. RETURNING gives you that data straight from the source, often saving you a whole round-trip to the database, which is a classic performance win.

Here’s the basic syntax. It couldn’t be simpler; you just tack it on at the end.

-- See what you're banning before you ban it (a good practice!)
UPDATE users
SET status = 'banned'
WHERE last_login < '2020-01-01'
RETURNING user_id, email, last_login;

-- Get the IDs of the rows you just deleted for your audit log
DELETE FROM stale_sessions
WHERE expires_on < now()
RETURNING session_id, user_id, created_date;

The Real Magic: RETURNING with Joins

This is where RETURNING graduates from “handy” to “mind-blowing”. You can use it in conjunction with FROM and joins to return data from other tables related to the rows you’re changing. This is phenomenally powerful for complex operations.

Scenario: You’re archiving orders from a main orders table to an orders_archive table. You want to delete the orders, but also return the customer names for a confirmation message. Without RETURNING, this is a multi-statement nightmare. With it, it’s elegant.

DELETE FROM orders
USING customers -- This is the FROM equivalent in a DELETE
WHERE orders.customer_id = customers.id
  AND orders.order_date < '2023-01-01'
RETURNING orders.id, customers.name, orders.order_date;

-- Want to update a product's price and immediately see its current
-- inventory level from another table in the same shot?
UPDATE products
SET price = price * 1.1
FROM inventory
WHERE products.id = inventory.product_id
RETURNING products.id, products.name, products.price, inventory.quantity;

Performance and the Weird Stuff

Is there a performance hit? Almost always negligible. You’re already touching these rows; the cost of sending their data back is marginal compared to the cost of the write operation itself. The benefit of avoiding another query almost always outweighs any tiny overhead.

Now, for the weird edge case. What if you UPDATE a row but the new values don’t actually change anything? Setting a status to ‘active’ when it’s already ‘active’? The database will still count it as an “affected row” because it’s technically updated. However, RETURNING will show you the new values, which are, amusingly, identical to the old ones. It’s a bit pedantic, but it’s correct behavior.

Best Practices and Pitfalls

  1. Be Specific: Don’t use RETURNING * unless you genuinely need every column. It’s wasteful. Explicitly list the columns you need (RETURNING id, name). Your future self (and your database) will thank you.

  2. The Primary Key is Your Friend: Always include the primary key in your RETURNING clause. It’s the only guaranteed way to uniquely identify the row that was changed, especially if the update itself alters the values you might use to identify it.

  3. Check for Nothing: Your application code must handle the scenario where zero rows are updated or deleted. In this case, RETURNING will, quite sensibly, return nothing. Don’t assume you’ll always get a result.

  4. It’s Not for Every Database: This is the big one. RETURNING is a PostgreSQL superstar. It’s its home turf. MySQL? Nope. Its closest equivalent is a separate SELECT after the query. SQL Server has OUTPUT, which is conceptually similar but with its own quirky syntax. SQLite has RETURNING as of version 3.35.0. Always check your database’s documentation. This is one of those features that will make you deeply resentful of other databases once you get used to it in Postgres.

In short, use RETURNING relentlessly. It makes your code more robust, more efficient, and easier to reason about. It turns a blind command into a conversation with your database. And that’s a conversation worth having.