DELETE with RETURNING clause in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of DELETE with RETURNING on single row

Given the table employees with columns id, name, and department, what will be the output of this query?

DELETE FROM employees WHERE id = 3 RETURNING name;

A [{"name": "John"}]

B []

C [{"name": "Alice"}]

D [{"id": 3, "name": "John", "department": "Sales"}]

💡 Hint

The RETURNING clause returns the deleted rows matching the condition.

The query deletes the employee with id 3 and returns the name of that employee. If the employee with id 3 is John, the output is [{"name": "John"}].

📝 Syntax

intermediate

Identify the syntax error in DELETE with RETURNING

Which option contains a syntax error in the DELETE statement with RETURNING clause?

A DELETE FROM orders WHERE order_id = 10 RETURNING *;

B DELETE orders WHERE order_id = 10 RETURNING *;

C DELETE FROM orders WHERE order_id = 10 RETURNING order_id;

D DELETE FROM orders WHERE order_id = 10 RETURNING order_date;

💡 Hint

Check the DELETE statement syntax carefully.

Option B is missing the FROM keyword after DELETE, which causes a syntax error.

❓ optimization

advanced

Optimizing DELETE with RETURNING for large tables

You want to delete all rows from logs older than 30 days and return their log_id. Which query is the most efficient?

A DELETE FROM logs RETURNING log_id WHERE log_date < NOW() - INTERVAL '30 days';

B SELECT log_id FROM logs WHERE log_date < NOW() - INTERVAL '30 days'; DELETE FROM logs WHERE log_date < NOW() - INTERVAL '30 days';

C DELETE FROM logs WHERE log_date < NOW() - INTERVAL '30 days' RETURNING log_id;

D DELETE FROM logs WHERE log_date < NOW() - INTERVAL '30 days';

💡 Hint

Consider how many times the table is scanned.

Option C deletes and returns the deleted rows in one operation, scanning the table only once. Option C scans twice, C has syntax error, and D does not return deleted rows.

🔧 Debug

advanced

Why does this DELETE with RETURNING return no rows?

Given the query:

DELETE FROM products WHERE price > 100 RETURNING *;

It returns an empty result set, but you know some products have price > 100. What is the most likely reason?

A The query deletes rows but does not return any columns.

B RETURNING clause is not supported in DELETE statements.

C The table <code>products</code> is empty.

D The WHERE condition is incorrect or does not match any rows.

💡 Hint

Check the WHERE condition carefully.

If no rows match the WHERE condition, DELETE deletes nothing and RETURNING returns an empty set.

🧠 Conceptual

expert

Understanding DELETE with RETURNING and transaction behavior

In PostgreSQL, if you run:

BEGIN;
DELETE FROM users WHERE active = false RETURNING id;
ROLLBACK;

What happens to the rows and what is the output of the DELETE statement?

A The DELETE returns the ids of deleted rows, but no rows are actually removed after rollback.

B The DELETE returns no rows because the transaction is rolled back immediately.

C The DELETE deletes rows permanently and returns their ids despite rollback.

D The DELETE causes an error because RETURNING cannot be used in transactions.

💡 Hint

Think about how transactions and rollback work in PostgreSQL.

The DELETE returns the ids of rows it would delete, but since the transaction is rolled back, no rows are actually removed from the table.

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