INSERT ON CONFLICT (upsert) in PostgreSQL - Step-by-Step Execution

Concept Flow - INSERT ON CONFLICT (upsert)

Start INSERT

↓

Check for conflict on unique key

↓

Run DO UPDATE

↓

Finish

When inserting a row, PostgreSQL checks if a conflict occurs on a unique key. If yes, it updates the existing row; if no, it inserts the new row.

Execution Sample

PostgreSQL

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

This query inserts a user with id=1 or updates the existing user's name and age if id=1 already exists.

Execution Table

Step	Action	Conflict Check	Conflict Found?	Resulting Action	Output
1	Attempt to insert (1, 'Alice', 30)	Check if id=1 exists	No	Insert new row	Row inserted: (1, 'Alice', 30)
2	Attempt to insert (1, 'Alice', 31)	Check if id=1 exists	Yes	Update existing row with new name and age	Row updated: (1, 'Alice', 31)
3	Attempt to insert (2, 'Bob', 25)	Check if id=2 exists	No	Insert new row	Row inserted: (2, 'Bob', 25)
4	Attempt to insert (2, 'Bobby', 26)	Check if id=2 exists	Yes	Update existing row with new name and age	Row updated: (2, 'Bobby', 26)
5	No more inserts	N/A	N/A	End of operations	Process complete

💡 All insert attempts processed; no more rows to insert.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
users table	empty	[(1, 'Alice', 30)]	[(1, 'Alice', 31)]	[(1, 'Alice', 31), (2, 'Bob', 25)]	[(1, 'Alice', 31), (2, 'Bobby', 26)]	[(1, 'Alice', 31), (2, 'Bobby', 26)]

Key Moments - 3 Insights

Why does the second insert with id=1 update the row instead of inserting a new one?

Because the ON CONFLICT clause detects that id=1 already exists (see execution_table row 2), so it runs the DO UPDATE action instead of inserting.

What happens if there is no conflict on the unique key?

The row is inserted normally without any update, as shown in execution_table rows 1 and 3.

How does PostgreSQL know which row to update on conflict?

It uses the unique key specified in ON CONFLICT (here, id) to find the existing row to update, as shown in the conflict check column.

Visual Quiz - 3 Questions

Look at the execution_table at step 2. What action is taken when inserting a row with id=1?

A Insert a new row

B Update the existing row

C Ignore the insert

D Raise an error

💡 Hint

Check the 'Resulting Action' column at step 2 in the execution_table.

At which step does the users table first contain two rows?

A After step 3

B After step 2

C After step 1

D After step 4

💡 Hint

Look at the variable_tracker row for 'users table' and see when two rows appear.

If the ON CONFLICT clause was removed, what would happen at step 2?

A The row would be inserted normally

B The existing row would be updated

C An error would occur due to duplicate key

D The insert would be ignored

💡 Hint

Without ON CONFLICT, duplicate unique keys cause errors in PostgreSQL.

Concept Snapshot

INSERT ... ON CONFLICT (unique_key) DO UPDATE
- Tries to insert a row
- If conflict on unique_key, updates existing row
- Prevents duplicate key errors
- Uses EXCLUDED to reference new values
- Useful for upsert operations

Full Transcript

This visual execution shows how PostgreSQL handles INSERT with ON CONFLICT (upsert). When inserting a row, it checks if a row with the same unique key exists. If no conflict, it inserts the new row. If conflict exists, it updates the existing row with new values. The execution table traces each insert attempt, showing conflict checks and actions taken. The variable tracker shows how the users table changes after each step. Key moments clarify why updates happen on conflict and how PostgreSQL identifies rows to update. The quiz tests understanding of conflict detection and actions. This helps beginners see how upsert works step-by-step.

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