Conditional INSERT with ON CONFLICT in PostgreSQL - Step-by-Step Execution

Concept Flow - Conditional INSERT with ON CONFLICT

Start INSERT

↓

Check if conflict on unique key

↓

Run ON CONFLICT action

↓

End

The database tries to insert a row. If a conflict on a unique key happens, it runs the ON CONFLICT action instead of erroring.

Execution Sample

PostgreSQL

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

Insert a user with id=1. If id=1 exists, update age only if new age is greater.

Execution Table

Step	Action	Conflict Check	Condition (WHERE)	Result	Table State
1	Try insert (1, 'Alice', 30)	No existing id=1	N/A	Insert succeeds	[{id:1, name:'Alice', age:30}]
2	Try insert (1, 'Alice', 35)	Conflict on id=1	users.age(30) < EXCLUDED.age(35) is True	Update age to 35	[{id:1, name:'Alice', age:35}]
3	Try insert (1, 'Alice', 25)	Conflict on id=1	users.age(35) < EXCLUDED.age(25) is False	Do nothing	[{id:1, name:'Alice', age:35}]
4	Try insert (2, 'Bob', 40)	No existing id=2	N/A	Insert succeeds	[{id:1, name:'Alice', age:35}, {id:2, name:'Bob', age:40}]

💡 All inserts processed; ON CONFLICT handled updates or skips as per condition.

Variable Tracker

Table State	Start	After Step 1	After Step 2	After Step 3	After Step 4
users	[]	[{id:1, name:'Alice', age:30}]	[{id:1, name:'Alice', age:35}]	[{id:1, name:'Alice', age:35}]	[{id:1, name:'Alice', age:35}, {id:2, name:'Bob', age:40}]

Key Moments - 2 Insights

Why does the third insert not update the age even though there is a conflict?

Because the WHERE condition users.age < EXCLUDED.age is false (35 < 25 is false), so the DO UPDATE action is skipped (see execution_table row 3).

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

The row is inserted normally without running the ON CONFLICT action (see execution_table rows 1 and 4).

Visual Quiz - 3 Questions

Look at the execution table, what is the age of user with id=1 after step 2?

A 35

B 25

C 30

D 40

💡 Hint

Check the 'Result' and 'Table State' columns at step 2 in the execution_table.

At which step does the insert skip updating due to the WHERE condition being false?

A Step 2

B Step 3

C Step 1

D Step 4

💡 Hint

Look for the row where 'Condition (WHERE)' is false in the execution_table.

If the WHERE condition was removed, what would happen at step 3?

A The insert would fail with conflict error

B The row would be deleted

C The age would update to 25

D Nothing would change

💡 Hint

Without WHERE, DO UPDATE always runs on conflict, see step 3 action.

Concept Snapshot

INSERT INTO table (columns) VALUES (values)
ON CONFLICT (conflict_target) DO UPDATE SET column = value
WHERE condition;

- Tries to insert row.
- If conflict on unique key, runs DO UPDATE.
- WHERE filters if update happens.
- Else inserts new row normally.

Full Transcript

This example shows how PostgreSQL handles conditional inserts with ON CONFLICT. When inserting a row, if a conflict occurs on a unique key, the database can update the existing row instead of erroring. The update only happens if the WHERE condition is true. Otherwise, the insert is skipped. This lets you insert new data or update existing data only when certain conditions are met, avoiding errors and unwanted overwrites.