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

The Big Idea

What if you could delete data and instantly see what you removed without extra steps?

The Scenario

Imagine you have a big list of customers in a spreadsheet. You want to remove some customers who haven't bought anything for a year, but you also want to keep a record of who you removed. Doing this by hand means deleting rows and then copying their details somewhere else.

The Problem

Manually deleting and then copying data is slow and easy to mess up. You might forget to save the removed customers' info, or accidentally delete the wrong rows. It's hard to keep track and can cause mistakes.

The Solution

The DELETE with RETURNING clause lets you delete rows and get back the deleted data in one step. This means you can remove unwanted records and immediately see or save their details without extra work.

Before vs After

✗ Before

SELECT * FROM customers WHERE last_purchase < '2022-01-01';
-- Then DELETE the rows separately

✓ After

DELETE FROM customers WHERE last_purchase < '2022-01-01' RETURNING *;

What It Enables

This lets you safely delete data while instantly capturing what was removed, making your work faster and more reliable.

Real Life Example

A store manager deletes old customer records but wants to email those customers a goodbye message. Using DELETE with RETURNING, they get the emails right after deletion to send the message.

Key Takeaways

Manually deleting and saving data is slow and risky.

DELETE with RETURNING deletes and returns data in one step.

This makes data cleanup safer and more efficient.