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

Using INSERT with RETURNING Clause in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to add new books to the books table and immediately get the unique ID assigned to each new book.

🎯 Goal: Learn how to insert new records into a PostgreSQL table and use the RETURNING clause to get the inserted row's ID.

📋 What You'll Learn

Create a table called books with columns id (serial primary key), title (text), and author (text).

Insert a new book with a specific title and author.

Use the RETURNING clause to get the id of the inserted book.

Store the returned id in a variable or use it in a query.

💡 Why This Matters

🌍 Real World

When adding new records to a database, you often need to know the unique ID assigned to them to link with other data or confirm the insertion.

💼 Career

Database developers and backend engineers frequently use INSERT with RETURNING to write efficient and clean code that handles new data entries and their identifiers.

1

Create the books table

Write a SQL statement to create a table called books with three columns: id as a serial primary key, title as text, and author as text.

PostgreSQL

-- Create the books table with id, title, and author columns
-- Your code here

Need a hint?

Use SERIAL PRIMARY KEY for the id column to auto-generate unique IDs.

2

Insert a new book record

Write a SQL INSERT statement to add a new book with the title 'The Great Gatsby' and author 'F. Scott Fitzgerald' into the books table.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author TEXT
);

-- Insert a new book with title 'The Great Gatsby' and author 'F. Scott Fitzgerald'
-- Your code here

Need a hint?

Use INSERT INTO books (title, author) VALUES (...) to add the book.

3

Use RETURNING clause to get the inserted id

Modify the previous INSERT statement to include the RETURNING id clause so that the query returns the id of the newly inserted book.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author TEXT
);

-- Insert the book and return the id of the new row
INSERT INTO books (title, author) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald') RETURNING id;

Need a hint?

Add RETURNING id after the VALUES clause to get the new book's id.

4

Use the returned id in a query

Write a SQL statement that inserts a new book with title '1984' and author 'George Orwell' into the books table, uses RETURNING id to get the new id, and then selects the inserted book by that id in a single query using a common table expression (CTE) named new_book.

PostgreSQL

CREATE TABLE books (
    id SERIAL PRIMARY KEY,
    title TEXT,
    author TEXT
);

-- Insert a new book '1984' by 'George Orwell' and select it by its returned id using a CTE
WITH new_book AS (
    INSERT INTO books (title, author) VALUES ('1984', 'George Orwell') RETURNING id
)
SELECT * FROM books WHERE id IN (SELECT id FROM new_book);

Need a hint?

Use a CTE with WITH new_book AS (INSERT ... RETURNING id) and then select from books using the returned id.