DELETE with RETURNING clause in PostgreSQL - Deep Dive

Overview - DELETE with RETURNING clause

What is it?

The DELETE statement in PostgreSQL removes rows from a table based on a condition. The RETURNING clause allows you to get back the data of the deleted rows immediately after deletion. This means you can see which rows were deleted without running a separate query. It helps combine deletion and retrieval in one step.

Why it matters

Without the RETURNING clause, you would have to run a separate SELECT query before or after deleting rows to know what was removed. This can cause extra work, slower performance, and possible errors if the data changes between queries. RETURNING makes deletion safer and more efficient by giving immediate feedback on what was deleted.

Where it fits

Before learning DELETE with RETURNING, you should understand basic SQL DELETE statements and SELECT queries. After this, you can explore more advanced data modification commands like UPDATE with RETURNING or using RETURNING in complex transactions.

Mental Model

Core Idea

DELETE with RETURNING lets you remove rows and instantly see which rows were deleted in one command.

Think of it like...

It's like throwing away old papers but catching the important details on a clipboard as you toss them, so you know exactly what you discarded.

┌───────────────┐
│   DELETE      │
│   FROM table  │
│   WHERE cond  │
│   RETURNING * │
└──────┬────────┘
       │
       ▼
┌─────────────────────────────┐
│ Rows matching condition are  │
│ deleted and their data sent  │
│ back as query result rows.   │
└─────────────────────────────┘

Build-Up - 7 Steps

1

Foundation

Basic DELETE statement usage

Concept: Learn how to remove rows from a table using DELETE with a condition.

The DELETE statement removes rows from a table that match a condition. For example, to delete users older than 30: DELETE FROM users WHERE age > 30; This removes all rows where the age column is greater than 30.

Result

Rows matching the condition are removed from the table.

Understanding how DELETE works is essential before adding RETURNING, as RETURNING builds on this basic operation.

2

Foundation

Understanding query results and SELECT

Concept: Know how SELECT retrieves data from tables to understand what RETURNING returns.

SELECT queries fetch rows from tables based on conditions. For example: SELECT * FROM users WHERE age > 30; This shows all users older than 30 without changing the data.

Result

You get a list of rows matching the condition without modifying the table.

RETURNING returns rows like SELECT does, but after a DELETE operation, combining retrieval with modification.

3

Intermediate

Using RETURNING to get deleted rows

🤔Before reading on: do you think RETURNING returns the deleted rows before or after deletion? Commit to your answer.

Concept: RETURNING returns the data of rows that were just deleted, letting you see what was removed.

Add RETURNING * to a DELETE statement to get all columns of deleted rows: DELETE FROM users WHERE age > 30 RETURNING *; This deletes the rows and returns their full data as query output.

Result

You see the exact rows deleted, including all their column values.

Knowing that RETURNING returns data after deletion helps you safely capture deleted information without extra queries.

4

Intermediate

Selecting specific columns with RETURNING

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

Concept: RETURNING can return specific columns, not just all columns, to get only the data you need.

You can specify columns after RETURNING to limit output. For example: DELETE FROM users WHERE age > 30 RETURNING id, name; This deletes rows but returns only the id and name columns of deleted rows.

Result

The output shows only the selected columns of deleted rows.

Being able to choose columns reduces data transfer and focuses on relevant information.

5

Intermediate

Combining DELETE RETURNING with application logic

🤔Before reading on: do you think RETURNING can be used to update other tables or just for display? Commit to your answer.

Concept: RETURNING lets applications immediately use deleted data for further processing, like logging or cascading actions.

For example, after deleting orders, you can capture deleted order IDs: DELETE FROM orders WHERE status = 'cancelled' RETURNING order_id; Your application can then log these IDs or update related tables based on them.

Result

Deleted row data is available instantly for other operations.

RETURNING bridges deletion and subsequent logic, making workflows more efficient and consistent.

6

Advanced

RETURNING in complex DELETE with joins

🤔Before reading on: do you think RETURNING works with DELETE statements involving joins? Commit to your answer.

Concept: In PostgreSQL, DELETE can use USING clause to join tables, and RETURNING still returns deleted rows from the main table.

Example deleting users who have no orders: DELETE FROM users u USING orders o WHERE u.id = o.user_id AND o.id IS NULL RETURNING u.id, u.name; This deletes users without orders and returns their info.

Result

You get deleted user details even when DELETE uses joins.

RETURNING works seamlessly with complex DELETE queries, enabling powerful data manipulation and feedback.

7

Expert

Performance and transactional behavior of RETURNING

🤔Before reading on: do you think RETURNING affects transaction speed or locking behavior? Commit to your answer.

Concept: RETURNING executes as part of the DELETE transaction, returning data without extra queries, but large outputs can impact performance.

RETURNING sends deleted rows immediately within the same transaction. This avoids race conditions but can increase network load if many rows are returned. Also, locks held during DELETE remain until transaction ends. Use RETURNING wisely for large deletes or batch in chunks.

Result

You get immediate deleted data with consistent transaction state but must consider performance tradeoffs.

Understanding RETURNING's transactional nature helps optimize deletion workflows and avoid unexpected slowdowns.

Under the Hood

When a DELETE with RETURNING runs, PostgreSQL first identifies rows matching the condition. It then marks these rows for deletion but before finalizing, it collects the requested columns from these rows. This data is sent back as the query result. The actual row removal happens within the same transaction, ensuring atomicity. RETURNING does not require a separate SELECT, so it avoids extra scanning or locking.

Why designed this way?

RETURNING was designed to reduce round-trips between client and server, improving efficiency and consistency. Before RETURNING, developers had to SELECT rows before DELETE, risking data changes between queries. RETURNING ensures the deleted data snapshot is accurate and immediate, simplifying application logic and reducing errors.

┌───────────────┐
│ DELETE command│
│ with condition│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Identify rows │
│ to delete     │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Collect data  │
│ for RETURNING │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Send data back│
│ as query      │
│ result        │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Finalize      │
│ deletion      │
│ in transaction│
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does RETURNING return rows before or after they are deleted? Commit to your answer.

Common Belief: RETURNING returns the rows before they are deleted, so the data is still in the table.

Reality: RETURNING returns the data of rows as they are being deleted, after they are marked for deletion but before the transaction commits.

Why it matters: Thinking RETURNING returns pre-deletion data can cause confusion about data visibility and lead to incorrect assumptions about transaction state.

Quick: Can RETURNING be used with any SQL command? Commit to your answer.

Common Belief: RETURNING works only with DELETE statements.

Reality: RETURNING also works with INSERT and UPDATE statements in PostgreSQL to return affected rows.

Why it matters: Limiting RETURNING to DELETE misses its broader usefulness in data modification commands, reducing its power in applications.

Quick: Does RETURNING increase the risk of data inconsistency? Commit to your answer.

Common Belief: Using RETURNING can cause inconsistent data because it returns deleted rows separately.

Reality: RETURNING is part of the same atomic transaction as DELETE, so it guarantees consistent and accurate data reflecting the deletion.

Why it matters: Misunderstanding transactional guarantees can lead to avoiding RETURNING and writing more complex, error-prone code.

Quick: Does RETURNING always improve performance? Commit to your answer.

Common Belief: RETURNING always makes DELETE faster because it combines queries.

Reality: RETURNING reduces round-trips but returning large amounts of data can slow down the operation and increase network load.

Why it matters: Assuming RETURNING is always faster can cause performance issues in large batch deletes if not used carefully.

Expert Zone

1

RETURNING can return computed expressions or functions, not just columns, allowing dynamic data capture during deletion.

2

When used inside triggers or stored procedures, RETURNING can help chain complex data workflows atomically.

3

RETURNING output respects row-level security policies, so it only returns rows the user is allowed to see.

When NOT to use

Avoid RETURNING when deleting very large numbers of rows if you do not need the deleted data, as it can increase memory and network usage. Instead, use plain DELETE for bulk removals. For complex cascading deletes, consider using foreign key ON DELETE actions or separate logging tables.

Production Patterns

In production, RETURNING is often used to log deleted records, synchronize caches, or update related tables immediately after deletion. It is common in APIs to return deleted resource details for client confirmation. Also used in batch jobs to track processed rows without extra queries.

Connections

Transactions

RETURNING works within transactions to ensure atomicity and consistency of data changes.

Understanding transactions helps grasp why RETURNING data is reliable and reflects the exact deleted rows at that moment.

Change Data Capture (CDC)

RETURNING can be used to capture deleted data changes immediately, similar to CDC systems that track data modifications.

Knowing CDC concepts clarifies how RETURNING supports real-time data auditing and synchronization.

Functional Programming

RETURNING resembles returning values from functions after side effects, combining action and result retrieval.

Seeing DELETE with RETURNING as a function that returns output after changing state helps understand its design elegance.

Common Pitfalls

#1 Expecting RETURNING to work without a WHERE clause and returning only some rows.

Wrong approach:

DELETE FROM users RETURNING id WHERE age > 30;

Correct approach:

DELETE FROM users WHERE age > 30 RETURNING id;

Root cause:

Misunderstanding SQL syntax order causes invalid queries and errors.

#2 Using RETURNING to fetch deleted rows but forgetting to handle empty results when no rows match.

Wrong approach:

DELETE FROM orders WHERE status = 'nonexistent' RETURNING *; -- assuming always returns rows

Correct approach:

Check if RETURNING result is empty before processing to avoid errors or incorrect assumptions.

Root cause:

Assuming DELETE always deletes rows leads to runtime errors or logic bugs.

#3 Returning too many columns or all columns unnecessarily, causing performance issues.

Wrong approach:

DELETE FROM logs WHERE created < '2020-01-01' RETURNING *;

Correct approach:

DELETE FROM logs WHERE created < '2020-01-01' RETURNING id, created;

Root cause:

Not optimizing data retrieval increases network and memory usage.

Key Takeaways

DELETE with RETURNING lets you delete rows and immediately see which rows were removed in one command.

RETURNING returns data after rows are marked for deletion but before the transaction commits, ensuring accuracy.

You can specify which columns to return, making data retrieval efficient and focused.

RETURNING works inside transactions, guaranteeing consistent and atomic data changes.

Using RETURNING wisely improves application logic by reducing extra queries and enabling immediate use of deleted data.

Practice

1. What does the RETURNING clause do in a DELETE statement in PostgreSQL?

easy

A. It counts the number of rows in the table.

B. It prevents rows from being deleted.

C. It updates rows instead of deleting them.

D. It returns the rows that were deleted.

Solution

1. Step 1: Understand DELETE operation

   The DELETE statement removes rows from a table based on a condition.

2. Step 2: Role of RETURNING clause

   The RETURNING clause outputs the rows that were deleted, showing their data immediately.

3. Final Answer:

   It returns the rows that were deleted. -> Option D

4. Quick Check:

   RETURNING shows deleted rows [OK]

Hint: RETURNING shows deleted rows immediately [OK]

Common Mistakes:

• Thinking RETURNING prevents deletion
• Confusing RETURNING with UPDATE
• Assuming RETURNING counts rows

2. Which of the following is the correct syntax to delete rows from users where age < 18 and return all deleted rows?

easy

A. DELETE FROM users WHERE age < 18 RETURNING *;

B. DELETE users WHERE age < 18 RETURNING *;

C. DELETE FROM users RETURNING * WHERE age < 18;

D. DELETE FROM users WHERE age < 18 RETURNING all;

Solution

1. Step 1: Check DELETE syntax

   The correct DELETE syntax is: DELETE FROM table WHERE condition.

2. Step 2: Use RETURNING clause correctly

   RETURNING * must come after WHERE clause to return all columns of deleted rows.

3. Final Answer:

   DELETE FROM users WHERE age < 18 RETURNING *; -> Option A

4. Quick Check:

   Correct DELETE syntax with RETURNING * [OK]

Hint: DELETE FROM ... WHERE ... RETURNING *; is correct syntax [OK]

Common Mistakes:

• Omitting FROM keyword
• Placing RETURNING before WHERE
• Using RETURNING all instead of *

3. Given the table products with rows:
{id:1, name:'Pen', stock:10}, {id:2, name:'Pencil', stock:0}, {id:3, name:'Eraser', stock:5}
What will this query return?

DELETE FROM products WHERE stock = 0 RETURNING id, name;

medium

A. [{id:1, name:'Pen'}, {id:3, name:'Eraser'}]

B. []

C. [{id:2, name:'Pencil'}]

D. Syntax error

Solution

1. Step 1: Identify rows matching condition

   Only the product with stock = 0 is {id:2, name:'Pencil'}.

2. Step 2: DELETE removes matching rows and RETURNING outputs specified columns

   The query deletes that row and returns its id and name.

3. Final Answer:

   [{id:2, name:'Pencil'}] -> Option C

4. Quick Check:

   Deleted rows with stock=0 returned [OK]

Hint: RETURNING shows deleted rows matching WHERE [OK]

Common Mistakes:

• Returning all rows instead of matching ones
• Expecting deleted rows to remain in table
• Confusing syntax causing errors

4. What is wrong with this query?

DELETE FROM orders RETURNING * WHERE status = 'cancelled';

medium

A. The WHERE clause must come before RETURNING.

B. RETURNING * is not allowed in DELETE.

C. Missing semicolon at the end.

D. DELETE cannot filter rows.

Solution

1. Step 1: Check clause order in DELETE

   In PostgreSQL, WHERE clause must come before RETURNING clause.

2. Step 2: Identify error in query

   The query places RETURNING * before WHERE, which is invalid syntax.

3. Final Answer:

   The WHERE clause must come before RETURNING. -> Option A

4. Quick Check:

   WHERE before RETURNING in DELETE [OK]

Hint: WHERE clause always before RETURNING [OK]

Common Mistakes:

• Placing RETURNING before WHERE
• Thinking RETURNING is disallowed
• Ignoring clause order rules

5. You want to delete all employees from staff who have salary < 30000 and return their id and name. Which query correctly does this and also returns the count of deleted rows?

hard

A. DELETE FROM staff WHERE salary < 30000 RETURNING id, name, COUNT(*);

B. WITH deleted AS (DELETE FROM staff WHERE salary < 30000 RETURNING id, name) SELECT COUNT(*) FROM deleted;

C. DELETE FROM staff RETURNING id, name WHERE salary < 30000; SELECT COUNT(*) FROM staff;

D. DELETE FROM staff WHERE salary < 30000; SELECT id, name FROM staff RETURNING COUNT(*);

Solution

1. Step 1: Delete rows with condition and return columns

   Using DELETE ... WHERE salary < 30000 RETURNING id, name returns deleted rows.

2. Step 2: Use WITH clause to capture deleted rows and count them

   WITH deleted AS (...) lets us select COUNT(*) from deleted rows in one query.

3. Final Answer:

   WITH deleted AS (DELETE FROM staff WHERE salary < 30000 RETURNING id, name) SELECT COUNT(*) FROM deleted; -> Option B

4. Quick Check:

   Use WITH + RETURNING to delete and count [OK]

Hint: Use WITH clause to count deleted rows with RETURNING [OK]

Common Mistakes:

• Trying to use COUNT(*) inside RETURNING
• Placing WHERE after RETURNING
• Separating DELETE and count without capturing deleted rows