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

Need a 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

Need a 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

Need a 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

Need a hint?

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