INSERT ON CONFLICT (upsert) in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this UPSERT query?

Given the table users(id INT PRIMARY KEY, name TEXT, age INT) with initial data:

id | name  | age
---+-------+----
1  | Alice | 30
2  | Bob   | 25

What will be the content of the table after running this query?

INSERT INTO users (id, name, age) VALUES (2, 'Bobby', 26)
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;

PostgreSQL

INSERT INTO users (id, name, age) VALUES (2, 'Bobby', 26)
ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, age = EXCLUDED.age;

A

id | name  | age
---+-------+----
1  | Alice | 30
2  | Bob   | 25

B

id | name  | age
---+-------+----
1  | Alice | 26
2  | Bobby | 26

C

id | name  | age
---+-------+----
1  | Alice | 30
2  | Bobby | 26

D

id | name  | age
---+-------+----
1  | Alice | 30
2  | Bobby | 25

💡 Hint

Remember that ON CONFLICT (id) DO UPDATE updates the existing row with the new values.

The query tries to insert a row with id=2. Since that id already exists, it updates the existing row's name and age to the new values 'Bobby' and 26.

❓ query_result

intermediate

What happens when ON CONFLICT DO NOTHING is used?

Given the table products(id INT PRIMARY KEY, name TEXT, price NUMERIC) with data:

id | name     | price
---+----------+-------
1  | Chair    | 49.99
2  | Table    | 89.99

What will be the table content after running this query?

INSERT INTO products (id, name, price) VALUES (2, 'Desk', 99.99)
ON CONFLICT (id) DO NOTHING;

PostgreSQL

INSERT INTO products (id, name, price) VALUES (2, 'Desk', 99.99)
ON CONFLICT (id) DO NOTHING;

A

id | name   | price
---+--------+-------
1  | Chair  | 49.99
2  | Table  | 89.99

B

id | name  | price
---+-------+-------
1  | Chair | 49.99
2  | Desk  | 99.99

C

id | name   | price
---+--------+-------
1  | Chair  | 49.99
2  | Desk   | 89.99

D

id | name   | price
---+--------+-------
1  | Chair  | 49.99
2  | Table  | 99.99

💡 Hint

DO NOTHING means the insert is skipped if there is a conflict.

The insert tries to add a product with id=2, but since that id exists, the DO NOTHING clause skips the insert. The table remains unchanged.

📝 Syntax

advanced

Which option is the correct syntax for UPSERT with partial update?

Which of the following queries correctly updates only the price column on conflict for the items table?

A

INSERT INTO items (id, name, price) VALUES (1, 'Pen', 1.5)
ON CONFLICT (id) DO SET price = EXCLUDED.price;

B

INSERT INTO items (id, name, price) VALUES (1, 'Pen', 1.5)
ON CONFLICT (id) DO UPDATE price = EXCLUDED.price;

C

INSERT INTO items (id, name, price) VALUES (1, 'Pen', 1.5)
ON CONFLICT (id) UPDATE SET price = EXCLUDED.price;

D

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

💡 Hint

The DO UPDATE SET clause is required after ON CONFLICT.

Option D uses the correct syntax: ON CONFLICT (id) DO UPDATE SET column = value. Other options miss keywords or have wrong order.

🔧 Debug

advanced

Why does this UPSERT query fail with a syntax error?

Consider this query:

INSERT INTO orders (order_id, product, quantity) VALUES (10, 'Book', 3)
ON CONFLICT (order_id) DO UPDATE quantity = EXCLUDED.quantity;

Why does it cause a syntax error?

PostgreSQL

INSERT INTO orders (order_id, product, quantity) VALUES (10, 'Book', 3)
ON CONFLICT (order_id) DO UPDATE quantity = EXCLUDED.quantity;

A Missing the SET keyword after DO UPDATE

B Missing parentheses around conflict target

C VALUES clause is not allowed with ON CONFLICT

D EXCLUDED keyword is invalid here

💡 Hint

Check the syntax after DO UPDATE.

The DO UPDATE clause must be followed by SET before specifying columns to update. The query misses SET, causing syntax error.

🧠 Conceptual

expert

Which statement about INSERT ON CONFLICT is true?

Choose the correct statement about INSERT ON CONFLICT in PostgreSQL.

A ON CONFLICT DO UPDATE can reference the existing row using the EXCLUDED keyword

B ON CONFLICT requires specifying the conflict target columns or constraint name

C ON CONFLICT DO NOTHING will raise an error if a conflict occurs

D ON CONFLICT can only handle conflicts on primary keys, not unique constraints

💡 Hint

Think about what you must specify to use ON CONFLICT.

Option B is correct: you must specify the conflict target columns or constraint name for ON CONFLICT to work. Option B is false because it works with unique constraints too. Option B is false because EXCLUDED refers to the new row, not the existing one. Option B is false because DO NOTHING skips the insert without error.

Practice

1. What does the INSERT ON CONFLICT clause do in PostgreSQL?

easy

A. It inserts a new row or updates existing row if a conflict occurs.

B. It deletes rows that conflict with the new data.

C. It only inserts new rows and ignores conflicts without any update.

D. It creates a new table if a conflict is found.

Solution

1. Step 1: Understand the purpose and behavior of INSERT ON CONFLICT

   This clause helps handle duplicate key conflicts during insert operations by allowing an update instead of error. It either updates the existing row or ignores the insert, preventing errors.

2. Final Answer:

   It inserts a new row or updates existing row if a conflict occurs. -> Option A

3. Quick Check:

   INSERT ON CONFLICT = Insert or update [OK]

Hint: Think: insert new or update existing on conflict [OK]

Common Mistakes:

• Confusing ON CONFLICT with DELETE
• Assuming it only inserts without update
• Thinking it creates new tables

2. Which of the following is the correct syntax to update a column price on conflict for a table products with unique id?

easy

A. INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT DO NOTHING;

B. INSERT INTO products (id, price) VALUES (1, 100) ON DUPLICATE KEY UPDATE price = price + 1;

C. INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price;

D. INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT (id) DO DELETE;

Solution

1. Step 1: Check PostgreSQL syntax for upsert and identify correct usage of EXCLUDED

   PostgreSQL uses ON CONFLICT (column) DO UPDATE SET with EXCLUDED to refer to new values. INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price; correctly uses EXCLUDED.price to update the price on conflict of id.

2. Final Answer:

   INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price; -> Option C

3. Quick Check:

   Correct syntax uses ON CONFLICT (id) DO UPDATE SET with EXCLUDED [OK]

Hint: Use EXCLUDED.column to update on conflict [OK]

Common Mistakes:

• Using MySQL syntax ON DUPLICATE KEY
• Forgetting EXCLUDED keyword
• Using DO DELETE which is invalid

3. Given the table users(id INT PRIMARY KEY, name TEXT, age INT) with data:
(1, 'Alice', 30)
What will be the result of this query?

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 31) ON CONFLICT (id) DO UPDATE SET age = EXCLUDED.age RETURNING *;

medium

A. [] (empty result)

B. [{id:1, name:'Alice', age:31}]

C. Error: duplicate key value violates unique constraint

D. [{id:1, name:'Alice', age:30}]

Solution

1. Step 1: Understand the existing data/conflict and apply ON CONFLICT DO UPDATE

   Row with id=1 exists with age=30, new insert tries age=31 causing conflict on id. It updates age to EXCLUDED.age (31) and returns the updated row.

2. Final Answer:

   [{id:1, name:'Alice', age:31}] -> Option B

3. Quick Check:

   ON CONFLICT updates age to 31 and returns updated row [OK]

Hint: ON CONFLICT updates existing row and returns it [OK]

Common Mistakes:

• Expecting error on duplicate key
• Thinking old age remains unchanged
• Assuming empty result on update

4. Identify the error in this query:

INSERT INTO inventory (product_id, quantity) VALUES (10, 5) ON CONFLICT (product_id) DO UPDATE quantity = quantity + EXCLUDED.quantity;

medium

A. VALUES clause cannot be used with ON CONFLICT.

B. EXCLUDED keyword is not allowed in DO UPDATE.

C. ON CONFLICT must be followed by DO NOTHING, not DO UPDATE.

D. Missing SET keyword before update expressions.

Solution

1. Step 1: Review ON CONFLICT DO UPDATE syntax and identify missing SET keyword

   The DO UPDATE clause requires SET keyword before specifying column updates. The query misses SET before 'quantity = quantity + EXCLUDED.quantity', causing syntax error.

2. Final Answer:

   Missing SET keyword before update expressions. -> Option D

3. Quick Check:

   DO UPDATE requires SET before column assignments [OK]

Hint: Remember DO UPDATE SET column = value [OK]

Common Mistakes:

• Omitting SET keyword in DO UPDATE
• Misusing EXCLUDED keyword
• Confusing DO NOTHING and DO UPDATE

5. You want to insert a new user into users(id, name, score) but if the id exists, update the score only if the new score is higher. Which query correctly implements this?

hard

A. INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score WHERE EXCLUDED.score > users.score;

B. INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO UPDATE SET score = users.score WHERE EXCLUDED.score > users.score;

C. INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO NOTHING WHERE EXCLUDED.score > users.score;

D. INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score;

Solution

1. Step 1: Understand conditional update on conflict and use WHERE clause in DO UPDATE

   You want to update score only if new score is greater than existing score. INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score WHERE EXCLUDED.score > users.score; uses WHERE EXCLUDED.score > users.score to conditionally update score correctly.

2. Final Answer:

   INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score WHERE EXCLUDED.score > users.score; -> Option A

3. Quick Check:

   Conditional update uses WHERE clause in DO UPDATE [OK]

Hint: Use WHERE in DO UPDATE for conditional changes [OK]

Common Mistakes:

• Updating unconditionally without WHERE
• Using DO NOTHING with WHERE (invalid)
• Setting score to old value mistakenly