Definition
MERGE– The SQL-standard statement that compares a target table against a source and runs different actions per row:WHEN MATCHED THEN UPDATE,WHEN MATCHED THEN DELETE, andWHEN NOT MATCHED THEN INSERT.INSERT ... ON CONFLICT– PostgreSQL's focused upsert for the common insert-or-update of individual rows against a unique key, simpler than MERGE.ON DUPLICATE KEY UPDATE– MySQL's equivalent of PostgreSQL's ON CONFLICT for single-row upserts.- ETL reconciliation – The scenario where MERGE shines: a nightly feed must insert new rows, update changed rows, and delete removed rows in one atomic pass.
- Auto-increment gotcha – A conflicting upsert still consumes a sequence value before the conflict is detected, leaving permanent but harmless gaps in generated IDs.
- Gapless numbering – For legally sequential series like invoice numbers, use a dedicated counter row inside the transaction instead of a bare sequence.
Think of reconciling National Bank of India's account master against a nightly feed from a partner bank. MERGE is the full reconciliation clerk: for each incoming record, update if the account exists, insert if it is new, and delete if the feed says the account closed – all in one controlled pass. INSERT ON CONFLICT is the simpler clerk who only ever says "add this, or if it already exists, update it." The gotcha is that every time the simple clerk tries to add a row with a new ticket number from the ticket machine, that ticket is spent even if the add turns into an update, so the ticket numbers develop harmless gaps.
Understanding the Concepts
What is MERGE?
MERGE is the SQL-standard statement that takes a target table and a source (a table, a subquery, or a VALUES list), joins them on a condition, and for each resulting row chooses an action based on whether it matched. The WHEN MATCHED branch can UPDATE or DELETE. The WHEN NOT MATCHED branch can INSERT. You can have several WHEN branches with extra conditions.
-- MERGE: reconcile National Bank of India accounts against partner feed
MERGE INTO accounts AS target
USING partner_feed AS source
ON target.account_id = source.account_id
WHEN MATCHED AND source.status = 'CLOSED' THEN
DELETE
WHEN MATCHED THEN
UPDATE SET target.balance = source.balance,
target.holder_name = source.holder_name,
target.updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
INSERT (account_id, balance, holder_name, status, created_at)
VALUES (source.account_id, source.balance, source.holder_name, 'ACTIVE', CURRENT_TIMESTAMP);
Expected result:
- New accounts from the feed are inserted
- Existing accounts are updated with fresh balances
- Accounts marked CLOSED in the feed are deleted from the target
- All three actions happen in one atomic statement
What is INSERT ... ON CONFLICT?
INSERT ... ON CONFLICT is PostgreSQL's focused upsert. It handles the frequent case of "insert this row, but if it collides on a unique key, update it (or do nothing)." It is simpler to read, has fewer sharp edges, and is the natural choice for single-row or small-batch upserts from application code.
-- ON CONFLICT: idempotent customer import
INSERT INTO customers (pan, full_name, branch, email)
VALUES ('ABCDE1234F', 'Priya Sharma', 'Mumbai-Central', 'priya@nbi.co.in')
ON CONFLICT (pan)
DO UPDATE SET
full_name = EXCLUDED.full_name,
branch = EXCLUDED.branch,
email = EXCLUDED.email;
How is MERGE different from INSERT ... ON CONFLICT?
| Aspect | MERGE | INSERT ... ON CONFLICT |
|---|---|---|
| Standard | SQL standard (ISO) | PostgreSQL extension |
| Actions | Insert, update, and delete | Insert or update only |
| Branches | Multiple WHEN clauses with conditions | Single DO UPDATE or DO NOTHING |
| Source | Table, subquery, or VALUES list | VALUES list only |
| Use case | Complex ETL reconciliation | Single-row or small-batch upsert |
| PostgreSQL support | Version 15+ only | Available since version 9.5 |
| Concurrency | Early implementations had quirks | Battle-tested for concurrent upserts |
| MySQL equivalent | Supported natively | ON DUPLICATE KEY UPDATE |
-- ON CONFLICT: simple single-row upsert (common in application code)
INSERT INTO daily_balance (account_id, balance_date, total_amount)
VALUES ('SAV2001', CURRENT_DATE, 50000.00)
ON CONFLICT (account_id, balance_date)
DO UPDATE SET total_amount = daily_balance.total_amount + EXCLUDED.total_amount;
-- MERGE: batch reconciliation (common in ETL pipelines)
MERGE INTO daily_balance AS target
USING (VALUES ('SAV2001', DATE '2024-01-15', 50000.00),
('CUR3001', DATE '2024-01-15', 75000.00)) AS source(account_id, balance_date, amount)
ON target.account_id = source.account_id AND target.balance_date = source.balance_date
WHEN MATCHED THEN
UPDATE SET total_amount = target.total_amount + source.amount
WHEN NOT MATCHED THEN
INSERT (account_id, balance_date, total_amount)
VALUES (source.account_id, source.balance_date, source.amount);
When should you use MERGE?
Use MERGE when the operation is genuinely multi-action – the source feed implies some rows must be inserted, some updated, and some deleted – or for set-based ETL reconciliations. A typical banking scenario is a nightly partner-bank feed where new accounts are opened, existing balances are updated, and closed accounts are removed, all in one controlled pass.
When should you use INSERT ... ON CONFLICT?
Use ON CONFLICT (or MySQL's ON DUPLICATE KEY UPDATE) when you just need an idempotent insert-or-update of a single row or a small batch from application code. It is simpler, more readable, and battle-tested for concurrent workloads.
package com.bank.service;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
public class CustomerImportService {
private static final String UPSERT_SQL = """
INSERT INTO customers (pan, full_name, branch, email)
VALUES (?, ?, ?, ?)
ON CONFLICT (pan)
DO UPDATE SET full_name = EXCLUDED.full_name,
branch = EXCLUDED.branch,
email = EXCLUDED.email
""";
public void importCustomer(Connection conn, String pan, String name,
String branch, String email) throws SQLException {
try (PreparedStatement ps = conn.prepareStatement(UPSERT_SQL)) {
ps.setString(1, pan);
ps.setString(2, name);
ps.setString(3, branch);
ps.setString(4, email);
ps.executeUpdate();
}
}
}
What is the auto-increment gotcha?
When a table has a serial or identity column backed by a sequence, the engine draws the next sequence value as part of attempting the insert, before it discovers the unique-key conflict that diverts the row into the update branch. Sequences are non-transactional by design – they do not roll back – so that drawn value is lost, leaving a gap in the generated identifiers.
-- Demonstrate the sequence gap
CREATE TABLE loan_applications (
id SERIAL PRIMARY KEY,
pan VARCHAR(10) UNIQUE,
amount NUMERIC(15,2)
);
INSERT INTO loan_applications (pan, amount) VALUES ('ABCDE1234F', 500000.00)
ON CONFLICT (pan) DO UPDATE SET amount = EXCLUDED.amount;
-- id = 1 assigned
INSERT INTO loan_applications (pan, amount) VALUES ('ABCDE1234F', 750000.00)
ON CONFLICT (pan) DO UPDATE SET amount = EXCLUDED.amount;
-- id = 2 consumed by sequence, but conflict routes to update. id 2 is gone.
INSERT INTO loan_applications (pan, amount) VALUES ('FGHIJ5678K', 300000.00)
ON CONFLICT (pan) DO UPDATE SET amount = EXCLUDED.amount;
-- id = 3 assigned. Gap at id 2.
Result:
id | pan | amount
----+------------+-----------
1 | ABCDE1234F | 750000.00
3 | FGHIJ5678K | 300000.00
How do you handle gapless numbering?
If you truly need gapless numbers – for a legally sequential invoice series at National Bank of India – you do not use a bare sequence. You use a dedicated counter row updated inside the transaction, accepting the contention that creates, or a separate numbering step that handles gaps deliberately.
-- Gapless invoice numbering with a counter row
BEGIN;
UPDATE invoice_counter SET next_number = next_number + 1
WHERE series = 'NBI-2024' RETURNING next_number;
-- Use the returned number as the invoice number
INSERT INTO invoices (invoice_number, account_id, amount)
VALUES ('NBI-2024-00042', 'SAV2001', 150000.00);
COMMIT;
What are common mistakes?
The most common mistake is using max(id) as a row count, which breaks when gaps exist. Another mistake is assuming generated IDs are contiguous and using that assumption in business logic.
-- WRONG: assumes contiguous IDs
SELECT MAX(id) - MIN(id) + 1 AS estimated_count FROM loan_applications;
-- Returns 3 but only 2 rows exist
-- CORRECT: count actual rows
SELECT COUNT(*) AS actual_count FROM loan_applications;
-- Returns 2
What should a senior engineer know?
A senior engineer knows to choose MERGE for multi-action ETL and ON CONFLICT for simple upserts, that MERGE only arrived in PostgreSQL 15 and had early concurrency quirks, that sequence gaps are normal and harmless, and that gapless numbering requires a dedicated counter row with transactional contention.
Visual Overview
┌─────────────────────────────────────────────────────┐ │ Choosing MERGE vs ON CONFLICT │ ├─────────────────────────────────────────────────────┤ │ │ │ ┌───────────────────────┐ │ │ │ Need insert + update │ │ │ │ + delete in one pass?│ │ │ └───────────┬───────────┘ │ │ ┌────┴────┐ │ │ │ Yes? │ │ │ └────┬────┘ │ │ Yes │ No │ │ ┌───────┴────────┐ │ │ ▼ ▼ │ │ ┌────────┐ ┌──────────────────┐ │ │ │ MERGE │ │ Simple upsert? │ │ │ │ (ETL) │ │ INSERT-or-UPDATE │ │ │ └────────┘ └────────┬─────────┘ │ │ ┌────┴────┐ │ │ │ Yes? │ │ │ └────┬────┘ │ │ Yes │ No │ │ ┌───────┴──────┐ │ │ ▼ ▼ │ │ ┌─────────────┐ ┌──────────┐ │ │ │ ON CONFLICT │ │ Plain │ │ │ │ (PostgreSQL)│ │ INSERT / │ │ │ │ ON DUPLICATE│ │ UPDATE │ │ │ │ KEY (MySQL) │ └──────────┘ │ │ └─────────────┘ │ │ │ ├─────────────────────────────────────────────────────┤ │ Auto-Increment Sequence Gap │ ├─────────────────────────────────────────────────────┤ │ Sequence: 1 → 2 → 3 → 4 → ... │ │ INSERT pan=A → id=1 (inserted) │ │ INSERT pan=A → id=2 (consumed) → conflict → UPDATE │ │ INSERT pan=B → id=3 (inserted) │ │ Result: ids 1, 3 present. id 2 = gap. │ └─────────────────────────────────────────────────────┘
Key Takeaway
MERGE is the SQL-standard statement that compares a target table against a source and runs a different action per row – WHEN MATCHED it can UPDATE or DELETE, and WHEN NOT MATCHED it can INSERT, with extra conditions allowed on each branch. That makes it the right tool for complex ETL where a single batch derived from a source feed must insert some rows, update others, and delete others, all in one atomic pass. INSERT with ON CONFLICT is deliberately narrower: it handles the common case of insert-this-row-but-update-if-it-collides on a unique key, it is simpler to read, and it is the natural choice for single-row or small-batch upserts from application code.
The choice is straightforward. Use MERGE when the operation is genuinely multi-action – a reconciliation where the source implies inserts, updates, and deletes together. Use ON CONFLICT, or MySQL's ON DUPLICATE KEY UPDATE, when you just need an idempotent single-row upsert. MERGE only arrived in PostgreSQL 15 and had some early concurrency quirks across databases, so for a plain concurrent upsert the battle-tested ON CONFLICT is often the safer pick.
The gotcha that both share is with auto-increment and sequences. When a table has a serial or identity column, the engine draws the next sequence value as part of attempting the insert, before it discovers the unique-key conflict. Sequences are non-transactional and never roll back, so the drawn value is permanently lost, leaving a gap. In our National Bank of India daily-balance table we saw IDs jump from two to four because a conflicting upsert consumed ID three – perfectly normal, because a sequence guarantees uniqueness and monotonicity, not gaplessness.
The practical rules are to never assume generated IDs are contiguous and never treat the maximum ID as a row count. When you genuinely need a gapless series like a legal invoice number, use a dedicated counter row updated inside the transaction instead of a bare sequence.
Remember one sentence: Use MERGE for multi-action ETL and INSERT ON CONFLICT for simple upserts, and expect sequence gaps because the auto-increment is consumed before the conflict is detected.
---