Advanced

UPSERT and MERGE

Synchronize incoming records with existing rows using PostgreSQL conflict handling, MERGE, and the equivalent patterns in MySQL, SQL Server, and Snowflake.

Est. 16–22 min Updated Suggest a fix

// Part 01

Make repeated loads safe

A daily import often contains both familiar business keys and new ones. A plain INSERT fails on a duplicate unique key; a separate “check, then insert or update” has a race because another transaction can change the row between the check and the write. An upsert means “insert this row, or update the conflicting row.” MERGE matches a source set to a target set and can perform different actions for matched and unmatched rows.

Example: an inventory feed contains a merchant SKU, the latest quantity, and the feed timestamp. Make the SKU unique, then merge incoming data against that key. The database constraint—not an earlier application query—defines what counts as a conflict.

💡 Note
Prerequisites: INSERT, UPDATE, unique constraints, and transactions. Objective: select a conflict key, write a repeatable synchronization statement, and recognize engine-specific syntax.

// Part 02

PostgreSQL UPSERT with ON CONFLICT

PostgreSQL’s INSERT ... ON CONFLICT handles a uniqueness conflict in one statement. The conflict target names a unique constraint or its columns. DO NOTHING ignores a duplicate; DO UPDATE updates the conflicting target row. The proposed row is available through the EXCLUDED pseudo-table.

PostgreSQL — insert or update a merchant’s inventory snapshot
CREATE TABLE inventory_snapshot (
  merchant_sku text PRIMARY KEY,
  quantity integer NOT NULL CHECK (quantity >= 0),
  source_updated_at timestamptz NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO inventory_snapshot (
  merchant_sku, quantity, source_updated_at
)
VALUES ('RICE-5KG', 42, TIMESTAMPTZ '2026-11-09 16:00:00-08')
ON CONFLICT (merchant_sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    source_updated_at = EXCLUDED.source_updated_at,
    updated_at = now()
WHERE EXCLUDED.source_updated_at > inventory_snapshot.source_updated_at;

The final WHERE prevents an older delayed feed row from overwriting newer state. When it evaluates false, PostgreSQL skips the update for that conflict. The uniqueness constraint still arbitrates competing inserts atomically. Use a target constraint that reflects the true identity of the row; email or SKU may not be unique unless the schema says so.

Variants: ignore a duplicate, or target a named constraint
-- Idempotently accept the first event for an external ID
INSERT INTO imported_events (external_id, payload)
VALUES ('evt-2048', '{"status":"paid"}'::jsonb)
ON CONFLICT (external_id) DO NOTHING;

-- A named unique constraint can be the target instead
INSERT INTO inventory_snapshot (merchant_sku, quantity, source_updated_at)
VALUES ('TEA-250G', 18, now())
ON CONFLICT ON CONSTRAINT inventory_snapshot_pkey DO UPDATE
SET quantity = EXCLUDED.quantity,
    source_updated_at = EXCLUDED.source_updated_at;

// Part 03

MERGE: match a source set to a target set

MERGE is useful when a batch has several records and different actions are required for matched and unmatched keys. PostgreSQL supports MERGE beginning with version 15. Use a staging table or a VALUES source, and make sure the source contains at most one row per target key—otherwise one target row can be matched more than once, which is an error or ambiguous business logic depending on the engine.

PostgreSQL 15+ — insert missing SKUs and update matched ones
MERGE INTO inventory_snapshot AS target
USING (
  VALUES
    ('RICE-5KG'::text, 45, TIMESTAMPTZ '2026-11-09 17:00:00-08'),
    ('TEA-250G'::text, 18, TIMESTAMPTZ '2026-11-09 17:00:00-08')
) AS source(merchant_sku, quantity, source_updated_at)
ON target.merchant_sku = source.merchant_sku
WHEN MATCHED AND source.source_updated_at > target.source_updated_at THEN
  UPDATE SET quantity = source.quantity,
             source_updated_at = source.source_updated_at,
             updated_at = now()
WHEN NOT MATCHED THEN
  INSERT (merchant_sku, quantity, source_updated_at)
  VALUES (source.merchant_sku, source.quantity, source.source_updated_at);

A MERGE source can be a staging table or a query result. Validate and deduplicate that source before the merge, and decide whether target rows missing from the source should remain untouched or be deleted. Deleting unmatched rows is a separate, potentially destructive business rule—not an automatic part of synchronization.

⚠️ Important
UPSERT is not a synonym for “replace the whole table.” Define which columns may change, how stale events are handled, whether absent rows are retained, and what happens when two writers race.

// Part 04

Dialect map: similar goal, different syntax

DatabaseCommon formImportant consideration
PostgreSQLINSERT ... ON CONFLICT (...) DO UPDATE / DO NOTHING; MERGE on newer supported versionsUse EXCLUDED for proposed values; the conflict target must match a unique constraint/index.
MySQLINSERT ... ON DUPLICATE KEY UPDATEAny conflicting unique key may trigger the update; be explicit about which unique keys can collide.
SQL ServerMERGE, or separate UPDATE/INSERT logic inside a transactionReview concurrency behavior and the target SQL Server guidance; test under realistic concurrent writes.
SnowflakeMERGE INTO target USING source ON ... WHEN MATCHED / NOT MATCHEDDeduplicate source rows by match key before merging; define matched updates and insert columns explicitly.

MySQL’s update clause is not PostgreSQL’s ON CONFLICT. In current MySQL syntax, an inserted-row alias can be used to refer to incoming values; older examples using VALUES(column) may be deprecated in newer releases. SQL Server and Snowflake support MERGE with their own exact syntax and semantics. Treat the table as a map, not copy-paste SQL; check the deployed engine and version.

MySQL-style example — verify the exact version syntax
CREATE TABLE inventory_snapshot_mysql (
  merchant_sku VARCHAR(64) PRIMARY KEY,
  quantity INT NOT NULL,
  source_updated_at DATETIME NOT NULL
);

INSERT INTO inventory_snapshot_mysql
  (merchant_sku, quantity, source_updated_at)
VALUES ('RICE-5KG', 45, CURRENT_TIMESTAMP)
AS incoming
ON DUPLICATE KEY UPDATE
  quantity = incoming.quantity,
  source_updated_at = incoming.source_updated_at;

// Part 05

Design for idempotency, constraints, and concurrency

An idempotent load can be retried without creating duplicate business records or corrupting state. A unique key is the foundation, but it does not define whether “last write wins,” whether the newest source timestamp wins, or whether an event should be applied once. Store a stable source event ID for event ingestion, and use a monotonic version or source timestamp when delayed data can arrive.

Keep the transaction boundary around related work. An upsert of one row is atomic as a statement, but a workflow that updates inventory and writes an audit record may need a transaction so both changes succeed or fail together. Inspect row counts and returned values as appropriate, and ensure the application distinguishes inserted, updated, ignored, and stale records if that matters operationally.

PostgreSQL — stage a batch and keep only the newest row per key
WITH newest_per_sku AS (
  SELECT DISTINCT ON (merchant_sku)
         merchant_sku, quantity, source_updated_at
  FROM incoming_inventory
  ORDER BY merchant_sku, source_updated_at DESC
)
INSERT INTO inventory_snapshot AS target
  (merchant_sku, quantity, source_updated_at)
SELECT merchant_sku, quantity, source_updated_at
FROM newest_per_sku
ON CONFLICT (merchant_sku) DO UPDATE
SET quantity = EXCLUDED.quantity,
    source_updated_at = EXCLUDED.source_updated_at,
    updated_at = now()
WHERE EXCLUDED.source_updated_at > target.source_updated_at;

DISTINCT ON is PostgreSQL-specific. In another engine, rank staged rows with ROW_NUMBER() partitioned by key and keep row 1. This is not cosmetic: a source containing multiple versions for one key must be reduced to the one row the business rule wants before a merge.

// Part 06

Common mistakes in upsert workflows

1. Checking then inserting in separate statements

Two concurrent sessions can both observe “missing” and then race to insert. Put the unique constraint in the database and use an atomic conflict-aware write.

2. Updating every column blindly

An import may not own every target field. Update only fields managed by that source, and use a timestamp/version guard to reject stale data.

3. Merging duplicate source keys

Deduplicate or rank the staging data before matching. Decide which version wins instead of depending on accidental row order.

4. Assuming nulls and unique keys behave alike everywhere

NULL uniqueness semantics and which key triggers an update differ by engine. Prefer non-null business keys and read the engine-specific constraint rules.

// Part 07

Practice: make the load safe to rerun

Use a hypothetical incoming table with merchant_sku, quantity, and source_updated_at. Write down the unique key and stale-data policy before writing SQL.

  1. Write a PostgreSQL statement that inserts a new SKU or updates its quantity when the incoming timestamp is newer.
  2. Change the conflict action to ignore duplicate event IDs without modifying an existing row.
  3. Stage two updates for the same SKU and deduplicate them by source timestamp before loading.
  4. Sketch a MERGE for a batch. Which action handles matched rows? Which handles new rows? What if the source has duplicate keys?
  5. Translate the simple upsert to MySQL or your engine, then verify the exact syntax and unique-key behavior in its documentation.

🎯 Key Takeaways

  • ✓A unique constraint defines conflict identity; application-side check-then-insert is race-prone.
  • ✓PostgreSQL ON CONFLICT is an atomic insert-or-update mechanism; EXCLUDED refers to proposed values.
  • ✓MERGE expresses actions for matched and unmatched source rows, but source keys must be unambiguous.
  • ✓Use timestamps or versions to avoid overwriting newer data with stale arrivals.
  • ✓Update only the columns the feed owns and keep related writes in a transaction.
  • ✓MySQL, SQL Server, and Snowflake differ in syntax and edge cases; test against the production engine.

Next

Next, extend grouped reports beyond one level of totals with ROLLUP, CUBE, and GROUPING SETS.

Lesson 59 → ROLLUP, CUBE and GROUPING SETS

HELP US IMPROVE

How was this lesson?

Your note is shared with the Chaduvuko team for review. Please don’t include personal or sensitive information.

Choose your feedback

Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.

Share

Discussion

0

Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.

Continue with GitHub
Loading...