Conditional INSERT with ON CONFLICT in PostgreSQL - Mini Project: Build & Apply

Conditional INSERT with ON CONFLICT in PostgreSQL

📖 Scenario: You are managing a small library database. You want to add new books to the books table. However, if a book with the same isbn already exists, you want to update its copies count only if the new count is higher than the existing one.

🎯 Goal: Build a SQL query that inserts a new book into the books table. If a conflict occurs on the isbn column, update the copies column only if the new value is greater than the existing value.

📋 What You'll Learn

Create a books table with columns isbn (text, primary key), title (text), and copies (integer).

Insert a new book with specific isbn, title, and copies values.

Use ON CONFLICT on the isbn column to handle duplicates.

Update the copies column only if the new copies value is greater than the existing one.

💡 Why This Matters

🌍 Real World

Managing inventory or catalog data where duplicates may occur and updates depend on conditions is common in libraries, stores, and many applications.

💼 Career

Understanding conditional inserts and conflict handling is essential for database administrators and backend developers to maintain data integrity and efficiency.

1

Create the books table

Write a SQL statement to create a table called books with columns: isbn as text and primary key, title as text, and copies as integer.

PostgreSQL

-- Your code here to create the books table

Need a hint?

Use CREATE TABLE with isbn as the primary key.

2

Prepare the insert values

Write a SQL INSERT INTO books statement to insert a book with isbn '978-0132350884', title 'Clean Code', and copies 3.

PostgreSQL

CREATE TABLE books (
    isbn TEXT PRIMARY KEY,
    title TEXT,
    copies INTEGER
);
-- Your code here to insert the book

Need a hint?

Use INSERT INTO books (isbn, title, copies) VALUES (...) with the exact values.

3

Add ON CONFLICT clause with conditional update

Modify the INSERT INTO books statement to add ON CONFLICT (isbn) DO UPDATE that updates copies only if the new copies value is greater than the existing one. Use EXCLUDED.copies to refer to the new value and books.copies for the existing value.

PostgreSQL

CREATE TABLE books (
    isbn TEXT PRIMARY KEY,
    title TEXT,
    copies INTEGER
);

-- Your code here to add ON CONFLICT with conditional update
INSERT INTO books (isbn, title, copies) VALUES ('978-0132350884', 'Clean Code', 3);

Need a hint?

Use ON CONFLICT (isbn) DO UPDATE SET copies = CASE WHEN EXCLUDED.copies > books.copies THEN EXCLUDED.copies ELSE books.copies END.

4

Complete the conditional INSERT statement

Add the title column update in the ON CONFLICT clause to keep the existing title unchanged during conflict. Complete the full SQL statement with the conditional update on copies and no change on title.

PostgreSQL

CREATE TABLE books (
    isbn TEXT PRIMARY KEY,
    title TEXT,
    copies INTEGER
);

-- Your code here to complete the ON CONFLICT clause with title unchanged
INSERT INTO books (isbn, title, copies) VALUES ('978-0132350884', 'Clean Code', 3)
ON CONFLICT (isbn) DO UPDATE SET copies = 
    CASE WHEN EXCLUDED.copies > books.copies THEN EXCLUDED.copies ELSE books.copies END;

Need a hint?

Set title = books.title in the DO UPDATE SET clause to keep the title unchanged.