PostgreSQL 15 introduced SQL MERGE, a set-based command for applying conditional changes to a target table from a source relation. It can insert, update, or delete rows according to whether source rows match target rows and which ordered WHEN condition applies. It is useful for batch reconciliation, but it does not make INSERT ... ON CONFLICT obsolete.
What is the MERGE command in PostgreSQL 15?
PostgreSQL 15, released on October 13, 2022, added the SQL-standard MERGE command. The PostgreSQL Global Development Group described the release as including “the SQL standard MERGE command.” The release notes characterize it as a way to adjust one table to match another, similar to INSERT ... ON CONFLICT but more batch-oriented. PostgreSQL 15 announcement · PostgreSQL 15 release notes
A MERGE statement joins source data to a target table to form candidate change rows. PostgreSQL classifies each candidate as matched or not matched, then checks the statement’s WHEN clauses in the order written. The first eligible clause whose condition is true supplies the action; at most one action runs for each candidate. The action can insert, update, or delete data, depending on the applicable clause. PostgreSQL 15 documentation, MERGE chapter (PDF)
How do WHEN MATCHED and WHEN NOT MATCHED work?
The match status comes from the source-to-target join. A candidate is WHEN MATCHED when that join finds a target row; it is WHEN NOT MATCHED when it does not. The optional condition after a clause can further restrict when its action runs. Since PostgreSQL evaluates clauses in written order and runs no more than one action per candidate, put more specific cases before broader ones.
Windows Errors? Fix Them Before They Spread
Repair common Windows errors and clear accumulated junk for a smoother, more stable PC - no reinstall needed.Free scan · no reinstallCrashes, No Sound, or Screen Glitches?
Random freezes, missing sound and display glitches usually trace back to one bad driver. Find and replace yours safely.Free scan · under a minuteFor example, a reconciliation task might update a target row when a source key matches, and insert a row when no target key matches. A separate matched condition could choose a delete action when the incoming data indicates that a target row should be removed. The exact join and conditions define which candidates reach those actions; they should reflect the intended data rules.
How is MERGE different from INSERT … ON CONFLICT?
The practical distinction is the shape of the operation. MERGE is suited to expressing a batch of source-to-target decisions: classify joined candidates and choose among matched and unmatched actions. INSERT ... ON CONFLICT expresses what to do when an attempted insert conflicts with an existing row. PostgreSQL’s release notes call MERGE more batch-oriented, but do not claim it is always faster or preferable. Choose based on the operation you need to express and confirm behavior against the PostgreSQL version you deploy. PostgreSQL 15 release notes
What happens if multiple source rows match one target row?
PostgreSQL 15.7 release notes say MERGE now throws an error if a target row joins to more than one source row, as required by the SQL standard. This makes source-key uniqueness important: check for duplicate keys or deduplicate the source before merging when the join is meant to identify one source row per target row. PostgreSQL 15.7 release notes
Is PostgreSQL MERGE safe with concurrent updates?
Concurrency behavior has had version-specific fixes, so “PostgreSQL 15” alone is not enough information to assess an installation. PostgreSQL 15.3 fixed cases where a row being updated or deleted by MERGE had just been concurrently updated; those cases could cause a crash, the wrong action, or no action. PostgreSQL 15.15 later fixed a MERGE UPDATE lock-and-retry issue that could return incorrect results under multiple concurrent updates. Consult the release notes for the exact minor version deployed, and test using the application’s isolation level, triggers, and partitioning. PostgreSQL 15.3 release notes · PostgreSQL 15.15 release notes
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREEClear out junk files and repair common Windows errorsFree Scan →What should logical-replication users check?
For a target table participating in logical replication, PostgreSQL 15.15 added missing replica-identity checks for relevant MERGE operations that may update or delete published rows. Check the release notes for your installed minor version when using MERGE on published tables. PostgreSQL 15.15 release notes
Quick Recap
Practical checklist before using MERGE
- Verify the source-to-target join condition identifies the intended target rows.
- Check or deduplicate source keys so multiple source rows do not join to the same target row.
- Order
WHENclauses deliberately; the first eligible true clause is the only action taken for a candidate. - Confirm the deployed PostgreSQL 15 minor version’s release notes, particularly when concurrent updates or logical replication are involved.
- Test with the application’s actual isolation level, triggers, and partitioning; the cited release notes establish specific fixes, not universal behavior for every configuration.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
