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

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

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

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;

Hint

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

Practice

1. What does the ON CONFLICT DO NOTHING clause do in a PostgreSQL INSERT statement?

easy

A. It skips inserting the row if a conflict occurs on a unique key.

B. It updates the existing row with new values on conflict.

C. It deletes the conflicting row before inserting the new one.

D. It raises an error and stops the insert operation.

Solution

1. Step 1: Understand ON CONFLICT behavior

   The ON CONFLICT DO NOTHING clause tells PostgreSQL to ignore the insert if a duplicate key conflict happens.

2. Step 2: Compare with other conflict actions

   Unlike DO UPDATE which modifies existing rows, DO NOTHING simply skips the insert without error.

3. Final Answer:

   It skips inserting the row if a conflict occurs on a unique key. -> Option A

4. Quick Check:

   ON CONFLICT DO NOTHING = skip insert on duplicate [OK]

Hint: DO NOTHING skips duplicates silently, no error thrown [OK]

Common Mistakes:

• Thinking DO NOTHING updates existing rows
• Assuming DO NOTHING deletes conflicting rows
• Believing DO NOTHING raises an error

2. Which of the following is the correct syntax to insert a row and update the existing row on conflict for the column id in PostgreSQL?

easy

A. INSERT INTO table_name (id, val) VALUES (1, 'a') ON DUPLICATE KEY UPDATE val = VALUES(val);

B. INSERT INTO table_name (id, val) VALUES (1, 'a') ON CONFLICT (id) DO NOTHING SET val = EXCLUDED.val;

C. INSERT INTO table_name (id, val) VALUES (1, 'a') ON CONFLICT UPDATE val = EXCLUDED.val;

D. INSERT INTO table_name (id, val) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val;

Solution

1. Step 1: Identify correct ON CONFLICT syntax

   PostgreSQL uses ON CONFLICT (column) DO UPDATE SET to update on conflict.

2. Step 2: Check syntax details

   INSERT INTO table_name (id, val) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val; correctly uses EXCLUDED.val to refer to the new value and specifies the conflict column.

3. Final Answer:

   INSERT INTO table_name (id, val) VALUES (1, 'a') ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val; -> Option D

4. Quick Check:

   ON CONFLICT (id) DO UPDATE SET val = EXCLUDED.val [OK]

Hint: Use ON CONFLICT (column) DO UPDATE SET col = EXCLUDED.col [OK]

Common Mistakes:

• Using MySQL syntax ON DUPLICATE KEY UPDATE
• Omitting conflict target column in ON CONFLICT
• Combining DO NOTHING with SET clause incorrectly

3. Given the table users(id SERIAL PRIMARY KEY, username TEXT UNIQUE, score INT), what will be the result of this query?

INSERT INTO users (username, score) VALUES ('alice', 10) ON CONFLICT (username) DO UPDATE SET score = users.score + EXCLUDED.score RETURNING *;

Assuming a row with username 'alice' and score 5 already exists.

medium

A. A new row with username 'alice' and score 10 is inserted.

B. The existing row's score is updated to 15 and returned.

C. The insert fails with a duplicate key error.

D. The existing row is deleted and a new row inserted.

Solution

1. Step 1: Understand ON CONFLICT DO UPDATE behavior

   The query updates the existing row where username='alice' because of the conflict on username.

2. Step 2: Calculate updated score

   The update sets score = users.score + EXCLUDED.score = 5 + 10 = 15, and returns the updated row.

3. Final Answer:

   The existing row's score is updated to 15 and returned. -> Option B

4. Quick Check:

   ON CONFLICT DO UPDATE adds scores = 15 [OK]

Hint: ON CONFLICT DO UPDATE can use existing and new values [OK]

Common Mistakes:

• Thinking a new row is inserted despite conflict
• Expecting an error on duplicate key
• Assuming the row is deleted before insert

4. Given the table products(id INT UNIQUE, name TEXT UNIQUE), identify the error in this PostgreSQL query:

INSERT INTO products (id, name) VALUES (1, 'Pen') ON CONFLICT DO UPDATE SET name = EXCLUDED.name;

medium

A. Missing conflict target column after ON CONFLICT.

B. EXCLUDED keyword is not valid in DO UPDATE.

C. VALUES clause should be after DO UPDATE.

D. DO UPDATE cannot be used without WHERE clause.

Solution

1. Step 1: Check ON CONFLICT syntax

   When using DO UPDATE, PostgreSQL requires specifying the conflict target column or constraint.

2. Step 2: Identify missing conflict target

   The query lacks (id) or constraint name after ON CONFLICT, causing syntax error.

3. Final Answer:

   Missing conflict target column after ON CONFLICT. -> Option A

4. Quick Check:

   ON CONFLICT needs target column for DO UPDATE [OK]

Hint: Always specify conflict column with DO UPDATE [OK]

Common Mistakes:

• Omitting conflict target column
• Misusing EXCLUDED keyword
• Assuming WHERE clause is mandatory

5. You want to insert a new user with email and last_login into a table users(email TEXT UNIQUE, last_login TIMESTAMP). If the email already exists, update last_login only if the new timestamp is more recent. Which query correctly implements this conditional update?

hard

A. INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT DO NOTHING WHERE EXCLUDED.last_login > users.last_login;

B. INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = users.last_login WHERE EXCLUDED.last_login > users.last_login;

C. INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login WHERE EXCLUDED.last_login > users.last_login;

D. INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login;

Solution

1. Step 1: Understand conditional update with WHERE clause

   PostgreSQL allows a WHERE clause in DO UPDATE to conditionally apply the update.

2. Step 2: Check condition for updating last_login

   INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login WHERE EXCLUDED.last_login > users.last_login; updates last_login only if the new timestamp is greater than the existing one, using WHERE EXCLUDED.last_login > users.last_login.

3. Final Answer:

   INSERT INTO users (email, last_login) VALUES ('[e-mail]', '2024-01-01') ON CONFLICT (email) DO UPDATE SET last_login = EXCLUDED.last_login WHERE EXCLUDED.last_login > users.last_login; -> Option C

4. Quick Check:

   Use WHERE in DO UPDATE for conditional update [OK]

Hint: Use WHERE in DO UPDATE to update conditionally [OK]

Common Mistakes:

• Forgetting WHERE clause for conditional update
• Using DO NOTHING with WHERE incorrectly
• Updating unconditionally without checking timestamps