RETURNING clause mental model in PostgreSQL - Mini Project: Build & Apply

Understanding the RETURNING Clause in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to add new books and immediately see the details of the books you just added without running a separate query.

🎯 Goal: Build a simple table and use the RETURNING clause in PostgreSQL to insert new book records and get back the inserted rows.

📋 What You'll Learn

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

Insert a new book record into the books table.

Use the RETURNING clause to get back the id, title, and author of the inserted book.

Insert multiple books at once and return their details using RETURNING.

💡 Why This Matters

🌍 Real World

RETURNING is useful when you want to add or change data and immediately know the new or changed values, like getting the new ID of a record you just added.

💼 Career

Database developers and backend engineers often use RETURNING to write efficient code that reduces extra queries and improves performance.

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 one book record

Write a SQL INSERT statement to add one book with 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 one book record into books
-- Your code here

Need a hint?

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

3

Insert one book and return inserted data

Modify the previous INSERT statement to include a RETURNING clause that returns the id, title, and author of the inserted book.

PostgreSQL

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

-- Insert one book and return id, title, author
INSERT INTO books (title, author) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald')
-- Your code here

Need a hint?

Add RETURNING id, title, author after the VALUES clause to get the inserted row details.

4

Insert multiple books and return their data

Write a SQL INSERT statement to add two books at once: '1984' by 'George Orwell' and 'To Kill a Mockingbird' by 'Harper Lee'. Use the RETURNING clause to return the id, title, and author of all inserted books.

PostgreSQL

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

INSERT INTO books (title, author) VALUES ('The Great Gatsby', 'F. Scott Fitzgerald') RETURNING id, title, author;

-- Insert two books and return their details
-- Your code here

Need a hint?

Use a single INSERT with multiple VALUES tuples and add RETURNING id, title, author at the end.