Conditional INSERT with ON CONFLICT in PostgreSQL - Deep Dive

Overview - Conditional INSERT with ON CONFLICT

What is it?

Conditional INSERT with ON CONFLICT is a way to add new data into a table but handle cases where the data might already exist. Instead of causing an error when a duplicate appears, it lets you decide what to do, like update the existing data or skip the insert. This helps keep your data clean and consistent without manual checks. It is especially useful when multiple users or processes try to add similar data at the same time.

Why it matters

Without this feature, inserting duplicate data would cause errors or require complex checks before every insert. This slows down applications and can lead to mistakes or data corruption. Conditional INSERT with ON CONFLICT makes database operations safer and more efficient, improving user experience and system reliability. It solves real problems in busy systems where data conflicts happen often.

Where it fits

Before learning this, you should understand basic SQL INSERT commands and the concept of unique constraints or primary keys in tables. After mastering this, you can explore advanced conflict handling, UPSERT patterns, and performance tuning for large-scale data operations.

Mental Model

Core Idea

Conditional INSERT with ON CONFLICT lets you try to add data but gracefully handle duplicates by updating or ignoring them in one command.

Think of it like...

Imagine mailing invitations to a party where some guests might already have RSVP'd. Instead of sending duplicate invites and causing confusion, you check if they replied and either update their response or skip sending again.

┌───────────────┐
│ Try to Insert │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Conflict?     │
├───────────────┤
│ Yes     No    │
└──┬─────┬──────┘
   │     │
   ▼     ▼
┌───────────────┐  ┌───────────────┐
│ Do UPDATE or  │  │ Insert Success│
│ DO NOTHING    │  └───────────────┘
└───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic INSERT Statement

Concept: Learn how to add new rows to a table using the INSERT command.

The INSERT command adds new data to a table. For example, to add a user: INSERT INTO users (id, name) VALUES (1, 'Alice'); This adds one row with id 1 and name Alice.

Result

A new row appears in the users table with the given values.

Understanding how to add data is the first step before handling conflicts or conditions.

2

Foundation

Unique Constraints and Conflicts

Concept: Understand that some columns must have unique values and what happens if you try to insert duplicates.

Tables often have unique constraints, like a user id or email that must be unique. If you try: INSERT INTO users (id, name) VALUES (1, 'Bob'); but id 1 already exists, the database will give an error about duplicate key.

Result

The insert fails with a duplicate key error.

Knowing why conflicts happen helps you see why conditional handling is needed.

3

Intermediate

Using ON CONFLICT DO NOTHING

🤔Before reading on: do you think ON CONFLICT DO NOTHING will insert the row or skip it when a duplicate exists? Commit to your answer.

Concept: Learn how to skip inserting a row if it conflicts with existing data.

You can write: INSERT INTO users (id, name) VALUES (1, 'Bob') ON CONFLICT DO NOTHING; If id 1 exists, this command skips the insert without error.

Result

No error occurs, and the existing row stays unchanged.

This lets you avoid errors and keep your program running smoothly when duplicates appear.

4

Intermediate

Using ON CONFLICT DO UPDATE

🤔Before reading on: do you think ON CONFLICT DO UPDATE replaces the whole row or only specified columns? Commit to your answer.

Concept: Learn how to update existing rows when a conflict happens during insert.

You can update specific columns on conflict: INSERT INTO users (id, name) VALUES (1, 'Bob') ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name; This changes the name of user with id 1 to Bob if it exists.

Result

The existing row is updated with new values instead of erroring.

This powerful feature combines insert and update in one step, simplifying code and improving efficiency.

5

Intermediate

Referencing EXCLUDED Values

Concept: Understand how to use the special EXCLUDED keyword to access the new data in the conflict update.

In the DO UPDATE clause, EXCLUDED refers to the row you tried to insert. For example: ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name means update the existing row's name to the new name you wanted to insert.

Result

You can selectively update columns based on the new data.

Knowing EXCLUDED lets you control exactly how conflicts change existing data.

6

Advanced

Conditional Updates in DO UPDATE

🤔Before reading on: can you add a condition to update only if the new data is different? Commit to your answer.

Concept: Learn to add WHERE clauses to update only when certain conditions are met.

You can write: ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name WHERE users.name IS DISTINCT FROM EXCLUDED.name; This updates only if the new name differs from the old one.

Result

Updates happen only when needed, avoiding unnecessary writes.

Conditional updates improve performance and reduce unwanted changes.

7

Expert

Performance and Locking Considerations

🤔Before reading on: do you think ON CONFLICT locks the whole table or just affected rows? Commit to your answer.

Concept: Understand how PostgreSQL handles locking and performance during ON CONFLICT operations.

ON CONFLICT uses row-level locks on conflicting rows, not the whole table. This allows concurrent inserts on different keys. However, heavy use can cause contention and slowdowns. Proper indexing and conflict target choice are critical for performance.

Result

Efficient conflict handling with minimal blocking if designed well.

Knowing internal locking helps design scalable, high-performance database operations.

Under the Hood

When you run an INSERT with ON CONFLICT, PostgreSQL tries to insert the row. If a unique constraint violation occurs, it catches the conflict and either skips the insert (DO NOTHING) or runs the DO UPDATE clause. The DO UPDATE clause can reference the new row via EXCLUDED and update the existing row atomically. This happens inside a transaction with row-level locks to ensure data integrity.

Why designed this way?

Before ON CONFLICT, developers had to write complex code to check for duplicates and update or insert accordingly, which was slow and error-prone. PostgreSQL introduced this feature to simplify common UPSERT patterns, improve concurrency, and reduce application complexity. The design balances ease of use with strong consistency guarantees.

┌───────────────┐
│ INSERT command│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Try Insert    │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Conflict?     │
├───────────────┤
│ Yes     No    │
└──┬─────┬──────┘
   │     │
   ▼     ▼
┌───────────────┐  ┌───────────────┐
│ DO UPDATE     │  │ Insert Success│
│ (with EXCLUDED│  └───────────────┘
│  row)         │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does ON CONFLICT DO NOTHING insert a new row if no conflict exists? Commit yes or no.

Common Belief: ON CONFLICT DO NOTHING always skips inserting the row.

Reality: ON CONFLICT DO NOTHING only skips the insert if a conflict occurs; otherwise, it inserts the new row.

Why it matters: Misunderstanding this can cause missing data because developers might think DO NOTHING disables all inserts.

Quick: Does ON CONFLICT DO UPDATE replace the entire existing row? Commit yes or no.

Common Belief: DO UPDATE replaces the whole existing row with the new one.

Reality: DO UPDATE only changes the columns you specify; other columns remain unchanged.

Why it matters: Assuming full replacement can lead to unintended data loss if updates are not carefully written.

Quick: Can ON CONFLICT handle conflicts on any column without unique constraints? Commit yes or no.

Common Belief: ON CONFLICT works on any column, even without unique constraints.

Reality: ON CONFLICT requires a unique constraint or exclusion constraint on the conflict target columns.

Why it matters: Trying to use ON CONFLICT without proper constraints causes errors and confusion.

Quick: Does ON CONFLICT guarantee no race conditions in concurrent inserts? Commit yes or no.

Common Belief: ON CONFLICT completely prevents race conditions in all concurrent inserts.

Reality: ON CONFLICT reduces race conditions but row-level locking and transaction isolation levels still affect concurrency behavior.

Why it matters: Overestimating guarantees can lead to subtle bugs in highly concurrent systems.

Expert Zone

1

ON CONFLICT DO UPDATE can cause triggers and rules to fire, which may have side effects not obvious at first glance.

2

The choice of conflict target (columns or constraint name) affects performance and correctness; using the exact unique index is best.

3

Using WHERE clauses in DO UPDATE can prevent unnecessary writes but may also cause the update to skip, leaving data unchanged unexpectedly.

When NOT to use

Avoid ON CONFLICT when you need complex conflict resolution logic that depends on multiple rows or external data. In such cases, use explicit SELECT-then-INSERT/UPDATE transactions or application-level logic.

Production Patterns

Commonly used for user registration to avoid duplicate accounts, inventory systems to update stock counts atomically, and logging systems to insert or update event counts efficiently.

Connections

Transactions

ON CONFLICT operations run inside transactions to ensure atomicity and consistency.

Understanding transactions helps grasp how ON CONFLICT maintains data integrity even under concurrent access.

Optimistic Concurrency Control

ON CONFLICT is a form of optimistic concurrency where conflicts are detected and resolved at commit time.

Knowing this connects database conflict handling to broader concurrency control strategies in computing.

Version Control Systems

Both handle conflicts by merging or choosing updates when multiple changes happen simultaneously.

Seeing conflict resolution in databases like ON CONFLICT as similar to merging code changes helps understand the concept across fields.

Common Pitfalls

#1 Ignoring the conflict target and writing ON CONFLICT without specifying columns or constraint.

Wrong approach:

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

Correct approach:

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

Root cause:

ON CONFLICT requires specifying the conflict target to know which constraint to check.

#2 Updating all columns blindly without checking if values changed.

Wrong approach:

ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name, email = EXCLUDED.email;

Correct approach:

ON CONFLICT (id) DO UPDATE SET name = EXCLUDED.name WHERE users.name IS DISTINCT FROM EXCLUDED.name;

Root cause:

Not using conditional updates can cause unnecessary writes and trigger unwanted side effects.

#3 Using ON CONFLICT on columns without unique constraints.

Wrong approach:

INSERT INTO users (name) VALUES ('Alice') ON CONFLICT (name) DO NOTHING;

Correct approach:

Add a unique constraint on name first:
ALTER TABLE users ADD CONSTRAINT unique_name UNIQUE (name);
Then use ON CONFLICT (name) DO NOTHING;

Root cause:

ON CONFLICT depends on unique or exclusion constraints to detect conflicts.

Key Takeaways

Conditional INSERT with ON CONFLICT lets you insert data safely by handling duplicates without errors.

You can choose to skip inserting duplicates or update existing rows in one simple command.

The EXCLUDED keyword gives access to the new data during conflict updates for precise control.

Proper use of conflict targets and conditional updates improves performance and correctness.

Understanding internal locking and transaction behavior helps design scalable, reliable database operations.

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