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.