ReviseAlgo Logo

Data Modification

UPSERT and MERGE

Handling write collisions by updating existing database values.

Last Updated: June 15, 2026 • 16 min read

1. Introduction

UPSERT (UPDATE or INSERT) is a pattern that inserts a new row if it doesn't exist, or updates the existing row if a conflict occurs on a unique constraint. PostgreSQL implements this with INSERT ... ON CONFLICT. The SQL standard defines MERGE for more complex conditional insert/update/delete logic, which PostgreSQL 15+ supports natively.

2. Why It Matters

  • Idempotent writes: UPSERT makes operations safe to retry — critical for message queues, webhook handlers, and distributed systems where duplicate delivery is common.
  • Counter aggregation: Increment counters (page views, vote counts) atomically without race conditions using ON CONFLICT DO UPDATE SET count = count + 1.
  • ETL pipelines: Data warehouse loads use MERGE to handle both new records and corrections to existing records in a single pass.
  • Configuration management: Store application settings that should be created on first use and updated on subsequent uses.

3. Real-World Analogy

Imagine a hotel front desk. A guest arrives: if they have a reservation (conflict), you update their room assignment. If they don't (no conflict), you create a new reservation. UPSERT is this exact logic — check for an existing record, and either update or insert accordingly — all in one atomic operation.

4. How It Works

PostgreSQL ON CONFLICT (UPSERT):

PostgreSQL 15+ MERGE statement:

5. Internal Architecture

6. Visual Explanation

7. Practical Example

Atomic counter with UPSERT:

Data synchronization with MERGE:

8. Common Mistakes

Missing unique index for conflict target

ON CONFLICT (col) requires a unique index or constraint on col. Without it, PostgreSQL raises an error: "there is no unique or exclusion constraint matching the ON CONFLICT specification."

Using EXCLUDED incorrectly

EXCLUDED refers to the row that was proposed for INSERT. In DO UPDATE SET col = EXCLUDED.col, the left side is the existing row's column, and EXCLUDED is the new value. Mixing these up leads to incorrect data.

Race conditions with SELECT + INSERT pattern

Never use SELECT ... if not exists INSERT else UPDATE — this has a race condition. Use ON CONFLICT instead, which is atomic.

Interview Insight

"How do you handle idempotent writes in a database?" — Use UPSERT (ON CONFLICT DO UPDATE) with a unique constraint on the idempotency key. This ensures duplicate requests produce the same result as a single request.

9. Quick Quiz

Q1: What does ON CONFLICT DO NOTHING return?

Answer: It skips the conflicting row silently. No error is raised, and no update occurs. The command returns successfully with a row count indicating how many rows were actually inserted (0 if all conflicted).

Q2: What is the EXCLUDED pseudo-table?

Answer: EXCLUDED is a special table reference in ON CONFLICT clauses that represents the row that was proposed for INSERT. It contains all the values from the VALUES clause or the SELECT query.

10. Scenario-Based Challenge

Challenge: Real-Time Leaderboard

Build a game leaderboard that handles concurrent score updates from thousands of players:

  1. Create a leaderboard(player_id, game_id, high_score, games_played) table with a composite unique constraint on (player_id, game_id).
  2. Write an UPSERT that updates high_score only if the new score is higher, and always increments games_played.
  3. Handle the case where a player's score should only be updated if it beats their previous record.
  4. Add a "last_active" timestamp that updates on every UPSERT.

11. Debugging Exercise

Find the bug in this UPSERT:

Issues:

  1. Wrong conflict target: The unique constraint should be on (user_id, key), not just (user_id). A user has multiple settings keys.
  2. Hardcoded value: Use EXCLUDED.value instead of the hardcoded string to make the query reusable for any setting value.

12. Interview Questions

Q1: How do you implement idempotent API writes with SQL?

A: Use an idempotency key (unique per API request) as a unique constraint. On retry, UPSERT with ON CONFLICT returns the existing result instead of creating a duplicate. Pattern: INSERT ... ON CONFLICT (idempotency_key) DO NOTHING.

Q2: What's the difference between ON CONFLICT and MERGE?

A: ON CONFLICT handles a single table's insert/update based on one unique constraint. MERGE can handle complex multi-way logic: different UPDATE actions based on different conditions, DELETE when matched, and INSERT when not matched — all in one statement. MERGE is more expressive but more complex.

Q3: Can UPSERT trigger unique constraint violations on other columns?

A: Yes. If the DO UPDATE SET clause modifies a column that has a separate unique constraint, the UPDATE itself can cause a conflict. PostgreSQL checks all constraints on the updated row, not just the original conflict target.

13. Production Considerations

  • Index overhead: Every unique constraint requires a unique B-tree index. High-write tables with many UPSERTs should minimize redundant unique indexes.
  • Lock contention: UPSERT acquires a row-level lock on the conflicting row. High-concurrency UPSERTs on the same key create a serialization bottleneck — consider application-level deduplication first.
  • WAL generation: Each UPSERT that results in an UPDATE generates WAL records just like a regular UPDATE. Heavy UPSERT workloads should tune WAL settings accordingly.
  • Partial unique indexes: Use CREATE UNIQUE INDEX ... WHERE condition for conflict targets that only apply to a subset of rows (e.g., only active records).
  • Monitoring: Track UPSERT hit rates (insert vs update ratio) to understand data flow patterns. A 90% update rate may indicate an opportunity to batch or cache at the application layer.