INSERT ON CONFLICT (upsert) in PostgreSQL - Mini Project: Build & Apply

Using INSERT ON CONFLICT (upsert) in PostgreSQL

📖 Scenario: You are managing a small bookstore database. You want to add new books or update the stock if the book already exists.

🎯 Goal: Build a PostgreSQL query that inserts a new book into the books table. If the book already exists (same isbn), update the stock count instead.

📋 What You'll Learn

Create a books table with columns isbn (primary key), title, and stock.

Insert a book with specific values.

Add a conflict handling clause to update stock if isbn already exists.

Use the INSERT ON CONFLICT syntax.

💡 Why This Matters

🌍 Real World

Managing inventory in a bookstore or any retail system where you need to add new items or update existing stock without duplicates.

💼 Career

Database administrators and backend developers often use UPSERT to maintain data integrity and simplify code that handles insert-or-update logic.

1

Create the books table

Write a SQL statement to create a table called books with columns: isbn as primary key of type VARCHAR(13), title as TEXT, and stock as INTEGER.

PostgreSQL

-- Write the CREATE TABLE statement for books
-- Your code here

Need a hint?

Use CREATE TABLE books and define isbn as primary key.

2

Insert a new book

Write a SQL INSERT statement to add a book with isbn '9780131103627', title 'The C Programming Language', and stock 5 into the books table.

PostgreSQL

CREATE TABLE books (
    isbn VARCHAR(13) PRIMARY KEY,
    title TEXT,
    stock INTEGER
);

-- Insert the book with given isbn, title, and stock
-- Your code here

Need a hint?

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

3

Add ON CONFLICT clause to update stock

Modify the previous INSERT statement to add an ON CONFLICT clause on isbn that updates the stock to the new value if the book already exists.

PostgreSQL

CREATE TABLE books (
    isbn VARCHAR(13) PRIMARY KEY,
    title TEXT,
    stock INTEGER
);

-- Insert or update stock if isbn exists
INSERT INTO books (isbn, title, stock) VALUES ('9780131103627', 'The C Programming Language', 5)
-- Your code here

Need a hint?

Use ON CONFLICT (isbn) DO UPDATE SET stock = EXCLUDED.stock to update stock on conflict.

4

Complete the UPSERT with title update

Extend the ON CONFLICT clause to also update the title to the new value on conflict, so both stock and title are updated if the book exists.

PostgreSQL

CREATE TABLE books (
    isbn VARCHAR(13) PRIMARY KEY,
    title TEXT,
    stock INTEGER
);

-- Complete the UPSERT to update both stock and title on conflict
INSERT INTO books (isbn, title, stock) VALUES ('9780131103627', 'The C Programming Language', 5)
ON CONFLICT (isbn) DO UPDATE SET stock = EXCLUDED.stock
-- Your code here

Need a hint?

Add title = EXCLUDED.title to the DO UPDATE SET clause.