Returning modified rows with RETURNING in PostgreSQL - Mini Project: Build & Apply

Returning Modified Rows with RETURNING in PostgreSQL

📖 Scenario: You manage a small bookstore database. You want to update the prices of some books and see which books were changed immediately after the update.

🎯 Goal: Build a SQL query that updates book prices and returns the updated rows using the RETURNING clause.

📋 What You'll Learn

Create a table called books with columns id (integer), title (text), and price (numeric).

Insert three specific books with given prices.

Set a price increase threshold variable.

Write an UPDATE query that increases prices by 10% for books priced below the threshold.

Use RETURNING to get the updated rows.

💡 Why This Matters

🌍 Real World

In real databases, you often want to update data and see what changed right away without running a separate SELECT query.

💼 Career

Knowing how to use RETURNING saves time and reduces errors when working with live data in PostgreSQL.

1

Create the books table and insert data

Create a table called books with columns id (integer), title (text), and price (numeric). Then insert these three rows exactly: (1, 'Learn SQL', 25.00), (2, 'Mastering Python', 40.00), and (3, 'Data Science Basics', 30.00).

PostgreSQL

-- Your code here to create table and insert rows

Need a hint?

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Set the price increase threshold

Create a variable called price_threshold and set it to 35.00 to decide which books will get a price increase.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT, price NUMERIC);
INSERT INTO books (id, title, price) VALUES
  (1, 'Learn SQL', 25.00),
  (2, 'Mastering Python', 40.00),
  (3, 'Data Science Basics', 30.00);
-- Your code here to set price_threshold

Need a hint?

Use the \set command in psql to create a variable.

3

Write the UPDATE query with price increase

Write an UPDATE statement that increases the price by 10% for all books where the current price is less than the variable :price_threshold.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT, price NUMERIC);
INSERT INTO books (id, title, price) VALUES
  (1, 'Learn SQL', 25.00),
  (2, 'Mastering Python', 40.00),
  (3, 'Data Science Basics', 30.00);

\set price_threshold 35.00
-- Your code here to update prices

Need a hint?

Use UPDATE with SET and a WHERE clause using the variable :price_threshold.

4

Add RETURNING clause to get updated rows

Modify the previous UPDATE query to include RETURNING id, title, price so it returns the updated rows with their new prices.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT, price NUMERIC);
INSERT INTO books (id, title, price) VALUES
  (1, 'Learn SQL', 25.00),
  (2, 'Mastering Python', 40.00),
  (3, 'Data Science Basics', 30.00);

\set price_threshold 35.00

UPDATE books
SET price = price * 1.10
WHERE price < :price_threshold
-- Your code here to add RETURNING clause

Need a hint?

Add RETURNING id, title, price at the end of the UPDATE statement.