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.