DELETE with RETURNING clause in PostgreSQL - Mini Project: Build & Apply

Using DELETE with RETURNING Clause in PostgreSQL

📖 Scenario: You manage a small library database. Sometimes, books are removed from the collection. You want to delete certain books and see which ones were removed immediately.

🎯 Goal: Build a SQL query that deletes books published before a certain year and returns the details of the deleted books using the RETURNING clause.

📋 What You'll Learn

Create a table named books with columns id, title, and year_published.

Insert specific book records into the books table.

Define a year threshold variable to decide which books to delete.

Write a DELETE query that removes books published before the threshold year and returns the deleted rows.

💡 Why This Matters

🌍 Real World

Deleting outdated or unwanted records from a database while immediately knowing which records were removed is common in inventory, library, or user management systems.

💼 Career

Database administrators and backend developers often use DELETE with RETURNING to maintain data integrity and audit changes efficiently.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and year_published (integer). Then insert these exact rows: (1, 'The Hobbit', 1937), (2, '1984', 1949), (3, 'The Catcher in the Rye', 1951), (4, 'To Kill a Mockingbird', 1960).

PostgreSQL

-- Create the books table and insert the given rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows exactly as given.

2

Set the year threshold for deletion

Create a variable called year_threshold and set it to 1950. This will be the cutoff year to delete books published before this year.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    year_published INTEGER NOT NULL
);

INSERT INTO books (id, title, year_published) VALUES
(1, 'The Hobbit', 1937),
(2, '1984', 1949),
(3, 'The Catcher in the Rye', 1951),
(4, 'To Kill a Mockingbird', 1960);

-- Set the year_threshold variable
-- Your code here

Hint

Use the psql command \set to define the variable year_threshold with the value 1950.

3

Write the DELETE query with RETURNING clause

Write a DELETE statement that deletes rows from books where year_published is less than the variable :year_threshold. Use the RETURNING clause to return the id, title, and year_published of the deleted rows.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    year_published INTEGER NOT NULL
);

INSERT INTO books (id, title, year_published) VALUES
(1, 'The Hobbit', 1937),
(2, '1984', 1949),
(3, 'The Catcher in the Rye', 1951),
(4, 'To Kill a Mockingbird', 1960);

\set year_threshold 1950

-- Write the DELETE query with RETURNING clause
-- Your code here

Hint

Use DELETE FROM books WHERE year_published < :year_threshold RETURNING id, title, year_published;

4

Complete the project with a comment explaining the output

Add a SQL comment explaining that the query returns the deleted books' details immediately after deletion.

PostgreSQL

CREATE TABLE books (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL,
    year_published INTEGER NOT NULL
);

INSERT INTO books (id, title, year_published) VALUES
(1, 'The Hobbit', 1937),
(2, '1984', 1949),
(3, 'The Catcher in the Rye', 1951),
(4, 'To Kill a Mockingbird', 1960);

\set year_threshold 1950

DELETE FROM books
WHERE year_published < :year_threshold
RETURNING id, title, year_published;

-- Add a comment explaining the output
-- Your code here

Hint

Add a comment starting with -- explaining that the query returns deleted books' details.

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