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

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

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

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

Hint

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

Practice

1. What is the main purpose of the RETURNING clause in PostgreSQL?

easy

A. To create a new table based on existing data

B. To return data from rows affected by INSERT, UPDATE, or DELETE in the same query

C. To backup the database automatically after changes

D. To schedule queries to run at a later time

Solution

1. Step 1: Understand the role of RETURNING clause

   The RETURNING clause is used to get data from rows that are changed by INSERT, UPDATE, or DELETE commands.

2. Step 2: Compare options with this role

   Only To return data from rows affected by INSERT, UPDATE, or DELETE in the same query correctly describes this behavior. Other options describe unrelated features.

3. Final Answer:

   To return data from rows affected by INSERT, UPDATE, or DELETE in the same query -> Option B

4. Quick Check:

   RETURNING returns changed rows data [OK]

Hint: RETURNING gives changed rows data immediately [OK]

Common Mistakes:

• Thinking RETURNING creates tables
• Confusing RETURNING with backup or scheduling
• Assuming RETURNING only works with SELECT

2. Which of the following is the correct syntax to use the RETURNING clause after an INSERT statement?

easy

A. INSERT INTO table_name RETURNING * VALUES (...);

B. RETURNING * INSERT INTO table_name VALUES (...);

C. INSERT INTO table_name VALUES (...) RETURNING *;

D. INSERT INTO table_name VALUES (...) OUTPUT *;

Solution

1. Step 1: Recall correct RETURNING syntax

   In PostgreSQL, RETURNING comes after the VALUES clause in an INSERT statement.

2. Step 2: Check each option

   'INSERT INTO table_name VALUES (...) RETURNING *;' correctly places RETURNING after VALUES. Placing RETURNING between table_name and VALUES is incorrect order. Starting the statement with RETURNING is invalid. Using OUTPUT instead of RETURNING is SQL Server syntax, not PostgreSQL.

3. Final Answer:

   INSERT INTO table_name VALUES (...) RETURNING *; -> Option C

4. Quick Check:

   RETURNING follows VALUES in INSERT [OK]

Hint: RETURNING always comes after VALUES in INSERT [OK]

Common Mistakes:

• Placing RETURNING before VALUES
• Using OUTPUT instead of RETURNING
• Putting RETURNING before INSERT

3. Given the table users(id SERIAL PRIMARY KEY, name TEXT, age INT), what will this query return?

UPDATE users SET age = age + 1 WHERE name = 'Alice' RETURNING id, age;

medium

A. The id and new age of all users named 'Alice' after increment

B. The id and old age of all users named 'Alice' before increment

C. All rows in the users table with updated ages

D. An error because RETURNING cannot be used with UPDATE

Solution

1. Step 1: Understand UPDATE with RETURNING

   UPDATE changes rows matching the condition and RETURNING returns specified columns from those updated rows after the change.

2. Step 2: Analyze the query

   The query increments age by 1 for users named 'Alice' and returns their id and new age values.

3. Final Answer:

   The id and new age of all users named 'Alice' after increment -> Option A

4. Quick Check:

   RETURNING shows updated rows data [OK]

Hint: RETURNING shows updated values, not old ones [OK]

Common Mistakes:

• Thinking RETURNING shows old values
• Assuming it returns all rows, not just updated
• Believing RETURNING is invalid with UPDATE

4. Consider this DELETE query:

DELETE FROM orders WHERE order_date < '2023-01-01' RETURNING order_id;

Which of the following is a common mistake that would cause this query to fail?

medium

A. Writing RETURNING before WHERE clause

B. Using DELETE without WHERE clause

C. Using RETURNING without specifying columns

D. Using RETURNING with SELECT statement

Solution

1. Step 1: Check correct RETURNING placement

   RETURNING must come after the WHERE clause in DELETE statements.

2. Step 2: Identify the error in options

   Writing RETURNING before WHERE clause describes placing RETURNING before WHERE, which is invalid syntax and causes failure.

3. Final Answer:

   Writing RETURNING before WHERE clause -> Option A

4. Quick Check:

   RETURNING comes after WHERE in DELETE [OK]

Hint: RETURNING always follows WHERE clause [OK]

Common Mistakes:

• Putting RETURNING before WHERE
• Omitting columns in RETURNING (allowed but returns all columns)
• Using RETURNING with SELECT (invalid)
• Deleting without WHERE (allowed but risky)

5. You want to insert multiple rows into a products table and immediately get the generated product_id and price of each inserted row. Which query correctly uses the RETURNING clause to achieve this?

hard

A. INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Book', 10) RETURN product_id, price;

B. INSERT INTO products (name, price) RETURNING product_id, price VALUES ('Pen', 1.5), ('Book', 10);

C. INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Book', 10) OUTPUT product_id, price;

D. INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Book', 10) RETURNING product_id, price;

Solution

1. Step 1: Recall correct syntax for multi-row INSERT with RETURNING

   RETURNING clause comes after all VALUES and specifies columns to return.

2. Step 2: Evaluate each option

   INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Book', 10) RETURNING product_id, price; correctly places RETURNING after VALUES with correct columns. INSERT INTO products (name, price) RETURNING product_id, price VALUES ('Pen', 1.5), ('Book', 10); has wrong order. INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Book', 10) OUTPUT product_id, price; uses OUTPUT which is not PostgreSQL syntax. INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Book', 10) RETURN product_id, price; uses RETURN which is invalid.

3. Final Answer:

   INSERT INTO products (name, price) VALUES ('Pen', 1.5), ('Book', 10) RETURNING product_id, price; -> Option D

4. Quick Check:

   RETURNING after VALUES returns inserted rows data [OK]

Hint: RETURNING follows VALUES and lists columns to return [OK]

Common Mistakes:

• Placing RETURNING before VALUES
• Using OUTPUT instead of RETURNING
• Using RETURN instead of RETURNING