INSERT ON CONFLICT (upsert) in PostgreSQL - Deep Dive

Overview - INSERT ON CONFLICT (upsert)

What is it?

INSERT ON CONFLICT, also known as upsert, is a way to insert a new row into a database table but update an existing row if a conflict occurs, such as a duplicate key. It helps avoid errors when trying to insert data that might already exist. This feature lets you combine insert and update actions into a single command.

Why it matters

Without upsert, you would need to write separate queries to check if a row exists before inserting or updating, which is slower and more error-prone. Upsert makes data handling simpler and more efficient, especially when multiple users or processes might change the data at the same time. It prevents conflicts and keeps your data consistent.

Where it fits

Before learning upsert, you should understand basic SQL INSERT and UPDATE commands and how unique constraints or primary keys work. After mastering upsert, you can explore advanced data integrity techniques, transaction control, and performance tuning in PostgreSQL.

Mental Model

Core Idea

Upsert is a single command that tries to insert data but updates existing data if a conflict happens.

Think of it like...

Imagine mailing a letter to a friend’s house. If they moved (conflict), instead of losing the letter, the post office updates the address and delivers it correctly.

┌───────────────┐
│ Try to Insert │
└──────┬────────┘
       │
       ▼
┌───────────────┐   Conflict?   ┌───────────────┐
│ Insert Success│─────────────▶│ Update Record │
└───────────────┘              └───────────────┘

Build-Up - 7 Steps

1

Foundation

Basic INSERT Command

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

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

Result

A new row is added to the table if the id 1 does not already exist.

Understanding how INSERT works is essential because upsert builds on this to handle conflicts.

2

Foundation

Unique Constraints and Conflicts

Concept: Understand what causes conflicts during insertion.

Tables often have unique constraints like primary keys. If you try to insert a row with a key that already exists, the database raises a conflict error to prevent duplicates.

Result

An error occurs if you insert a duplicate key, stopping the operation.

Knowing what triggers conflicts helps you see why upsert is needed to handle these cases gracefully.

3

Intermediate

INSERT ON CONFLICT Syntax

🤔Before reading on: do you think ON CONFLICT can only update existing rows or can it also ignore conflicts? Commit to your answer.

Concept: Learn the syntax to handle conflicts by updating or ignoring them.

PostgreSQL lets you write INSERT ... ON CONFLICT (column) DO UPDATE SET ... to update existing rows on conflict, or DO NOTHING to skip insertion if a conflict occurs.

Result

The command inserts a new row or updates the existing row without error.

Understanding the syntax unlocks the power to combine insert and update in one step.

4

Intermediate

Using DO UPDATE to Modify Rows

🤔Before reading on: do you think DO UPDATE can change all columns or only the conflicting key? Commit to your answer.

Concept: Learn how to specify which columns to update when a conflict happens.

You can set any columns to new values using DO UPDATE SET column = EXCLUDED.column, where EXCLUDED refers to the new data you tried to insert.

Result

The existing row is updated with new values from the insert attempt.

Knowing how to update specific columns lets you control data changes precisely during conflicts.

5

Intermediate

Using DO NOTHING to Skip Conflicts

Concept: Learn how to ignore conflicts and keep existing data unchanged.

Instead of updating, you can write ON CONFLICT (column) DO NOTHING to skip inserting if a conflict occurs, leaving the existing row as is.

Result

No error occurs, and the existing row remains unchanged.

This option is useful when you want to avoid duplicates but don't want to change existing data.

6

Advanced

Handling Multiple Conflicts and Constraints

🤔Before reading on: can ON CONFLICT handle conflicts on multiple columns at once? Commit to your answer.

Concept: Learn how to specify conflict targets and handle complex constraints.

You can specify one or more columns or constraint names in ON CONFLICT to target specific conflicts. For example, ON CONFLICT ON CONSTRAINT unique_email DO UPDATE ... handles conflicts on a named unique constraint.

Result

Conflicts on specified constraints are handled correctly with update or ignore.

Understanding conflict targets helps you write precise upsert commands for complex tables.

7

Expert

Performance and Concurrency Considerations

🤔Before reading on: do you think upsert always performs better than separate insert and update? Commit to your answer.

Concept: Learn how upsert behaves under heavy load and concurrent access.

Upsert reduces race conditions by combining insert and update atomically, but it can cause contention on indexes. Proper indexing and understanding locking behavior are important for performance.

Result

Upsert ensures data consistency but may need tuning for high concurrency.

Knowing the internal locking and performance tradeoffs helps you use upsert effectively in production.

Under the Hood

When you run INSERT ON CONFLICT, PostgreSQL tries to insert the row. If a unique constraint violation occurs, it catches the conflict and either updates the existing row or skips insertion based on your command. This happens atomically inside a transaction to prevent race conditions.

Why designed this way?

Before upsert, developers had to write complex code with separate SELECT, INSERT, and UPDATE statements, which caused race conditions and inefficiency. PostgreSQL introduced ON CONFLICT to simplify this common pattern and ensure atomicity and consistency.

┌───────────────┐
│ Start Insert  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Check Unique  │
│ Constraint    │
└──────┬────────┘
       │ No Conflict
       ▼
┌───────────────┐
│ Insert Row    │
└───────────────┘
       │
       ▼
     Done

If Conflict:
       │
       ▼
┌───────────────┐
│ ON CONFLICT   │
│ DO UPDATE or  │
│ DO NOTHING    │
└──────┬────────┘
       │
       ▼
     Done

Myth Busters - 4 Common Misconceptions

Quick: Does ON CONFLICT always update all columns of the existing row? Commit yes or no.

Common Belief: ON CONFLICT updates the entire existing row with the new data automatically.

Reality: ON CONFLICT only updates the columns you explicitly specify in the DO UPDATE SET clause.

Why it matters: Assuming all columns update can cause unexpected data loss or stale data if you forget to include some columns.

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

Common Belief: You can use ON CONFLICT on any column, even if it is not unique or indexed.

Reality: ON CONFLICT requires a unique constraint or index to detect conflicts; it cannot handle conflicts on non-unique columns.

Why it matters: Trying to use ON CONFLICT without proper constraints will cause errors or unexpected behavior.

Quick: Does DO NOTHING mean the insert silently fails? Commit yes or no.

Common Belief: DO NOTHING causes the insert to fail silently and rollback the transaction.

Reality: DO NOTHING skips the conflicting insert but does not cause an error or rollback; the transaction continues.

Why it matters: Misunderstanding this can lead to missing data inserts without realizing it.

Quick: Is upsert always faster than separate insert and update? Commit yes or no.

Common Belief: Upsert is always faster and better than running separate insert and update queries.

Reality: Upsert is atomic and safer but can be slower under heavy concurrency due to locking and index contention.

Why it matters: Assuming upsert is always best can lead to performance issues in high-load systems.

Expert Zone

1

Upsert can cause deadlocks if multiple transactions try to upsert the same rows in different orders, requiring careful transaction design.

2

Using EXCLUDED in DO UPDATE lets you reference the proposed new row, enabling complex conditional updates.

3

Partial unique indexes can be targeted in ON CONFLICT, allowing fine-grained control over conflict detection.

When NOT to use

Avoid upsert when you need to perform complex conditional logic before deciding to insert or update; in such cases, separate SELECT and conditional statements may be better. Also, for bulk inserts where conflicts are rare, batch inserts with error handling might be more efficient.

Production Patterns

In real systems, upsert is used for syncing data from external sources, caching layers, and event processing where data may arrive out of order or duplicated. It is combined with transactions and proper indexing to maintain consistency and performance.

Connections

Transactions

Upsert relies on transactions to ensure atomicity and consistency during insert or update operations.

Understanding transactions helps grasp how upsert prevents race conditions and partial updates.

Unique Constraints

Upsert depends on unique constraints to detect conflicts and decide when to update existing rows.

Knowing how unique constraints work clarifies why upsert can only handle conflicts on constrained columns.

Conflict Resolution in Distributed Systems

Upsert is a form of conflict resolution similar to how distributed systems reconcile data changes from multiple sources.

Recognizing this connection helps understand upsert as a practical solution to data consistency challenges beyond databases.

Common Pitfalls

#1 Trying to use ON CONFLICT without a unique constraint on the target column.

Wrong approach:

INSERT INTO users (email, name) VALUES ('[e-mail]', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

Correct approach:

CREATE UNIQUE INDEX unique_email ON users(email);
INSERT INTO users (email, name) VALUES ('[e-mail]', 'Alice') ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

Root cause:

ON CONFLICT requires a unique constraint or index to detect conflicts; missing this causes errors.

#2 Using DO UPDATE without specifying columns to update.

Wrong approach:

INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT (id) DO UPDATE;

Correct approach:

INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price;

Root cause:

DO UPDATE must specify how to update the existing row; omitting SET clause causes syntax errors.

#3 Assuming DO NOTHING inserts the row anyway.

Wrong approach:

INSERT INTO orders (id, status) VALUES (10, 'new') ON CONFLICT (id) DO NOTHING;

Correct approach:

INSERT INTO orders (id, status) VALUES (10, 'new') ON CONFLICT (id) DO UPDATE SET status = EXCLUDED.status;

Root cause:

DO NOTHING skips insertion on conflict; it does not insert or update the row.

Key Takeaways

INSERT ON CONFLICT (upsert) lets you insert new rows or update existing ones in a single atomic command.

It relies on unique constraints to detect conflicts and requires explicit instructions on how to update or ignore conflicts.

Upsert simplifies data handling, prevents race conditions, and improves efficiency compared to separate insert and update queries.

Understanding its syntax, behavior, and performance implications is essential for building reliable and scalable database applications.

Misusing upsert can cause data loss, errors, or performance problems, so careful design and testing are important.

Practice

1. What does the INSERT ON CONFLICT clause do in PostgreSQL?

easy

A. It inserts a new row or updates existing row if a conflict occurs.

B. It deletes rows that conflict with the new data.

C. It only inserts new rows and ignores conflicts without any update.

D. It creates a new table if a conflict is found.

Solution

1. Step 1: Understand the purpose and behavior of INSERT ON CONFLICT

   This clause helps handle duplicate key conflicts during insert operations by allowing an update instead of error. It either updates the existing row or ignores the insert, preventing errors.

2. Final Answer:

   It inserts a new row or updates existing row if a conflict occurs. -> Option A

3. Quick Check:

   INSERT ON CONFLICT = Insert or update [OK]

Hint: Think: insert new or update existing on conflict [OK]

Common Mistakes:

• Confusing ON CONFLICT with DELETE
• Assuming it only inserts without update
• Thinking it creates new tables

2. Which of the following is the correct syntax to update a column price on conflict for a table products with unique id?

easy

A. INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT DO NOTHING;

B. INSERT INTO products (id, price) VALUES (1, 100) ON DUPLICATE KEY UPDATE price = price + 1;

C. INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price;

D. INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT (id) DO DELETE;

Solution

1. Step 1: Check PostgreSQL syntax for upsert and identify correct usage of EXCLUDED

   PostgreSQL uses ON CONFLICT (column) DO UPDATE SET with EXCLUDED to refer to new values. INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price; correctly uses EXCLUDED.price to update the price on conflict of id.

2. Final Answer:

   INSERT INTO products (id, price) VALUES (1, 100) ON CONFLICT (id) DO UPDATE SET price = EXCLUDED.price; -> Option C

3. Quick Check:

   Correct syntax uses ON CONFLICT (id) DO UPDATE SET with EXCLUDED [OK]

Hint: Use EXCLUDED.column to update on conflict [OK]

Common Mistakes:

• Using MySQL syntax ON DUPLICATE KEY
• Forgetting EXCLUDED keyword
• Using DO DELETE which is invalid

3. Given the table users(id INT PRIMARY KEY, name TEXT, age INT) with data:
(1, 'Alice', 30)
What will be the result of this query?

INSERT INTO users (id, name, age) VALUES (1, 'Alice', 31) ON CONFLICT (id) DO UPDATE SET age = EXCLUDED.age RETURNING *;

medium

A. [] (empty result)

B. [{id:1, name:'Alice', age:31}]

C. Error: duplicate key value violates unique constraint

D. [{id:1, name:'Alice', age:30}]

Solution

1. Step 1: Understand the existing data/conflict and apply ON CONFLICT DO UPDATE

   Row with id=1 exists with age=30, new insert tries age=31 causing conflict on id. It updates age to EXCLUDED.age (31) and returns the updated row.

2. Final Answer:

   [{id:1, name:'Alice', age:31}] -> Option B

3. Quick Check:

   ON CONFLICT updates age to 31 and returns updated row [OK]

Hint: ON CONFLICT updates existing row and returns it [OK]

Common Mistakes:

• Expecting error on duplicate key
• Thinking old age remains unchanged
• Assuming empty result on update

4. Identify the error in this query:

INSERT INTO inventory (product_id, quantity) VALUES (10, 5) ON CONFLICT (product_id) DO UPDATE quantity = quantity + EXCLUDED.quantity;

medium

A. VALUES clause cannot be used with ON CONFLICT.

B. EXCLUDED keyword is not allowed in DO UPDATE.

C. ON CONFLICT must be followed by DO NOTHING, not DO UPDATE.

D. Missing SET keyword before update expressions.

Solution

1. Step 1: Review ON CONFLICT DO UPDATE syntax and identify missing SET keyword

   The DO UPDATE clause requires SET keyword before specifying column updates. The query misses SET before 'quantity = quantity + EXCLUDED.quantity', causing syntax error.

2. Final Answer:

   Missing SET keyword before update expressions. -> Option D

3. Quick Check:

   DO UPDATE requires SET before column assignments [OK]

Hint: Remember DO UPDATE SET column = value [OK]

Common Mistakes:

• Omitting SET keyword in DO UPDATE
• Misusing EXCLUDED keyword
• Confusing DO NOTHING and DO UPDATE

5. You want to insert a new user into users(id, name, score) but if the id exists, update the score only if the new score is higher. Which query correctly implements this?

hard

A. INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score WHERE EXCLUDED.score > users.score;

B. INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO UPDATE SET score = users.score WHERE EXCLUDED.score > users.score;

C. INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO NOTHING WHERE EXCLUDED.score > users.score;

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

Solution

1. Step 1: Understand conditional update on conflict and use WHERE clause in DO UPDATE

   You want to update score only if new score is greater than existing score. INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score WHERE EXCLUDED.score > users.score; uses WHERE EXCLUDED.score > users.score to conditionally update score correctly.

2. Final Answer:

   INSERT INTO users (id, name, score) VALUES (2, 'Bob', 50) ON CONFLICT (id) DO UPDATE SET score = EXCLUDED.score WHERE EXCLUDED.score > users.score; -> Option A

3. Quick Check:

   Conditional update uses WHERE clause in DO UPDATE [OK]

Hint: Use WHERE in DO UPDATE for conditional changes [OK]

Common Mistakes:

• Updating unconditionally without WHERE
• Using DO NOTHING with WHERE (invalid)
• Setting score to old value mistakenly