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.

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