UPSERT and MERGE
Synchronize incoming records with existing rows using PostgreSQL conflict handling, MERGE, and the equivalent patterns in MySQL, SQL Server, and Snowflake.
// 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.
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.
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.
// 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.
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.
// Part 04
Dialect map: similar goal, different syntax
| Database | Common form | Important consideration |
|---|---|---|
| PostgreSQL | INSERT ... ON CONFLICT (...) DO UPDATE / DO NOTHING; MERGE on newer supported versions | Use EXCLUDED for proposed values; the conflict target must match a unique constraint/index. |
| MySQL | INSERT ... ON DUPLICATE KEY UPDATE | Any conflicting unique key may trigger the update; be explicit about which unique keys can collide. |
| SQL Server | MERGE, or separate UPDATE/INSERT logic inside a transaction | Review concurrency behavior and the target SQL Server guidance; test under realistic concurrent writes. |
| Snowflake | MERGE INTO target USING source ON ... WHEN MATCHED / NOT MATCHED | Deduplicate 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.
// 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.
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.
- Write a PostgreSQL statement that inserts a new SKU or updates its quantity when the incoming timestamp is newer.
- Change the conflict action to ignore duplicate event IDs without modifying an existing row.
- Stage two updates for the same SKU and deduplicate them by source timestamp before loading.
- Sketch a
MERGEfor a batch. Which action handles matched rows? Which handles new rows? What if the source has duplicate keys? - 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.
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.
Spotted a mistake or something unclear? Suggest a fix on GitHub or email us. Select the text first and it is quoted for you.
Discussion
0Have a better approach? Found something outdated? Share it — your knowledge helps everyone learning here.
