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

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

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;

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);

Hint

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

Practice

1. What does the RETURNING clause do in a PostgreSQL INSERT statement?

easy

A. It creates a backup of the inserted data.

B. It deletes rows after insertion.

C. It updates existing rows instead of inserting.

D. It returns specified columns of the newly inserted rows immediately.

Solution

1. Step 1: Understand the purpose of RETURNING

   The RETURNING clause in PostgreSQL is used to get back specified columns from the rows that were just inserted.

2. Step 2: Compare with other options

   The other options describe actions unrelated to RETURNING. RETURNING does not delete, update, or backup data.

3. Final Answer:

   It returns specified columns of the newly inserted rows immediately. -> Option D

4. Quick Check:

   RETURNING returns inserted data [OK]

Hint: RETURNING fetches inserted row data instantly [OK]

Common Mistakes:

• Thinking RETURNING deletes or updates data
• Confusing RETURNING with SELECT
• Assuming RETURNING creates backups

2. Which of the following is the correct syntax to insert a row and return the generated id in PostgreSQL?

easy

A. INSERT INTO users VALUES ('Alice') RETURNING id;

B. INSERT INTO users (name) RETURN id VALUES ('Alice');

C. INSERT INTO users (name) VALUES ('Alice') RETURNING id;

D. INSERT INTO users (name) VALUES ('Alice') RETURN id;

Solution

1. Step 1: Check correct RETURNING syntax

   The correct syntax places RETURNING after VALUES and specifies the column to return, like RETURNING id.

2. Step 2: Validate each option

   INSERT INTO users (name) VALUES ('Alice') RETURNING id; matches the correct syntax. Placing RETURNING before VALUES or using RETURN instead of RETURNING is incorrect. INSERT INTO users VALUES ('Alice') RETURNING id; misses column list before VALUES.

3. Final Answer:

   INSERT INTO users (name) VALUES ('Alice') RETURNING id; -> Option C

4. Quick Check:

   RETURNING follows VALUES with column list [OK]

Hint: RETURNING goes right after VALUES clause [OK]

Common Mistakes:

• Placing RETURNING before VALUES
• Omitting column list in INSERT
• Using RETURN instead of RETURNING

3. Given the table products(id SERIAL PRIMARY KEY, name TEXT, price NUMERIC), what will this query return?

INSERT INTO products (name, price) VALUES ('Pen', 1.5) RETURNING id, price;

medium

A. The new product's id and price, e.g., (5, 1.5)

B. Only the id of the new product

C. An error because RETURNING can't return multiple columns

D. Nothing, INSERT does not return rows

Solution

1. Step 1: Understand RETURNING with multiple columns

   RETURNING can return one or more columns from the inserted row(s). Here, id and price are requested.

2. Step 2: Predict output

   The query inserts a new product and returns its auto-generated id and the price 1.5 as a row, e.g., (5, 1.5).

3. Final Answer:

   The new product's id and price, e.g., (5, 1.5) -> Option A

4. Quick Check:

   RETURNING multiple columns returns all requested [OK]

Hint: RETURNING can list multiple columns separated by commas [OK]

Common Mistakes:

• Assuming RETURNING returns only one column
• Expecting no output from INSERT
• Thinking RETURNING causes syntax error with multiple columns

4. What is wrong with this query?

INSERT INTO orders (product_id, quantity) VALUES (10, 3) RETURNING;

medium

A. VALUES clause is incorrectly formatted.

B. RETURNING clause is missing column names to return.

C. INSERT statement cannot have RETURNING clause.

D. product_id and quantity columns do not exist.

Solution

1. Step 1: Check RETURNING syntax

   RETURNING must specify at least one column or * to return. Here, it is empty, which is invalid syntax.

2. Step 2: Verify other parts

   VALUES clause and columns look correct. INSERT supports RETURNING. Assuming columns exist, no error there.

3. Final Answer:

   RETURNING clause is missing column names to return. -> Option B

4. Quick Check:

   RETURNING needs columns or * [OK]

Hint: RETURNING must specify columns or * [OK]

Common Mistakes:

• Leaving RETURNING empty
• Confusing RETURNING with WHERE
• Assuming RETURNING is optional but empty

5. You want to insert multiple rows into employees(name, department) and get back their generated id values. Which query correctly does this and returns all new ids?

hard

A. INSERT INTO employees (name, department) VALUES ('John', 'HR'), ('Jane', 'IT') RETURNING id;

B. INSERT INTO employees (name, department) RETURNING id VALUES ('John', 'HR'), ('Jane', 'IT');

C. INSERT INTO employees (name, department) VALUES ('John', 'HR'); RETURNING id; INSERT INTO employees (name, department) VALUES ('Jane', 'IT'); RETURNING id;

D. INSERT INTO employees (name, department) VALUES ('John', 'HR'), ('Jane', 'IT') RETURN id;

Solution

1. Step 1: Understand multi-row INSERT with RETURNING

   PostgreSQL allows inserting multiple rows in one statement and RETURNING returns all inserted rows' specified columns.

2. Step 2: Analyze each option

   INSERT INTO employees (name, department) VALUES ('John', 'HR'), ('Jane', 'IT') RETURNING id; correctly inserts two rows and returns their ids. INSERT INTO employees (name, department) RETURNING id VALUES ('John', 'HR'), ('Jane', 'IT'); misplaces RETURNING before VALUES. INSERT INTO employees (name, department) VALUES ('John', 'HR'); RETURNING id; INSERT INTO employees (name, department) VALUES ('Jane', 'IT'); RETURNING id; splits inserts and RETURNING incorrectly. INSERT INTO employees (name, department) VALUES ('John', 'HR'), ('Jane', 'IT') RETURN id; uses invalid RETURN keyword.

3. Final Answer:

   INSERT INTO employees (name, department) VALUES ('John', 'HR'), ('Jane', 'IT') RETURNING id; -> Option A

4. Quick Check:

   Multi-row INSERT with RETURNING returns all rows [OK]

Hint: Use RETURNING after multi-row VALUES to get all ids [OK]

Common Mistakes:

• Placing RETURNING before VALUES
• Splitting inserts instead of multi-row insert
• Using RETURN instead of RETURNING