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.