PostgreSQL 15 MERGE makes reconciliation rules explicit

Reconciling a source relation with a target table requires deciding which rows correspond before deciding what to change. Some source rows refer to existing targets, while others require insertion. PostgreSQL 15 makes those decisions expressible within the SQL-standard MERGE command, including conditional deletion of a matched target row.

The October 13 release announcement introduces MERGE alongside sorting improvements and new logical replication controls. The command deserves attention for its execution model. It joins a source to a target and applies ordered actions to candidate change rows. That model is more specific than a general promise to combine several SQL statements.

I default to PostgreSQL for relational work, and this is a useful addition to its vocabulary. A reconciliation rule is easier to inspect when the matching condition and the action conditions appear together. Its value depends on understanding those conditions precisely, especially when concurrent transactions can change the target.

Matching precedes action selection

The MERGE documentation shipped with PostgreSQL 15 describes a join producing candidate change rows. Each candidate receives a matched or not-matched classification. PostgreSQL then evaluates the applicable WHEN clauses in their written order and executes the first one whose condition is true. At most one action runs for a candidate.

The order is therefore part of the statement's meaning. A conditional clause can handle a narrow case before a later unconditional clause handles the remaining candidates of that kind. An unconditional clause makes a later clause of the same matched category unreachable, which PostgreSQL rejects. There is no automatic interpretation that the most specific condition should win regardless of position.

The source can be a table or a query. That lets a statement shape the incoming relation before matching it to the target. The join condition then establishes correspondence between the source row and the target row. Action conditions decide what the corresponding pair requires.

For illustration, assume an existing inventory table and a staged inventory_changes table with at most one row per item. Each change supplies an item identifier and the desired quantity. This statement removes an existing zero-quantity item, updates other existing items, and inserts positive quantities for missing items.

MERGE INTO inventory AS i
USING inventory_changes AS s
ON i.item_id = s.item_id
WHEN MATCHED AND s.quantity = 0 THEN
  DELETE
WHEN MATCHED THEN
  UPDATE SET quantity = s.quantity
WHEN NOT MATCHED AND s.quantity > 0 THEN
  INSERT (item_id, quantity)
  VALUES (s.item_id, s.quantity);

The example uses the staged quantity as the desired value, not a delta to add. The unconditional matched clause follows the deletion rule, so zero-quantity matched rows do not reach the update. An unmatched row with a nonpositive quantity has no qualifying action and produces no change.

The join condition controls whether a row exists for MERGE

The documentation warns about conditions in ON that refer only to target columns. Such a condition can alter whether a source row counts as matched. That changes which category of actions PostgreSQL considers, rather than simply narrowing an update.

For example, adding a target-only eligibility test to an identity match can make an existing target appear unmatched when it fails that eligibility test. A later not-matched insertion may then attempt to create another row with the same identity. The intended rule might instead be to leave the existing row unchanged. Placing that eligibility test in a matched action condition expresses the latter rule without changing correspondence.

This follows from the distinction between finding the target and deciding what to do with it. The source-target join answers the first question. Conditions attached to WHEN answer the second. Keeping an identity comparison in ON makes the reconciliation easier to reason about when an existing target fails some business condition.

The row classification is not a request to keep applying actions until the source and target reach a fixed point. Under the documented candidate model, one qualifying action handles the candidate. A delete does not then lead the same candidate through a not-matched insertion branch.

Duplicate source keys require a defined policy

The command documentation requires the join to produce at most one candidate change row for each target row. If several source rows identify the same target, later attempts to modify that target can fail. Repeated updates or deletes produce a cardinality violation. Repeated insertion can produce a uniqueness violation.

A staged batch with repeated item identifiers therefore needs a policy before it reaches the example statement. The policy might aggregate compatible changes or reject the duplicate input. Which policy is correct depends on the meaning of the source. MERGE does not establish that the first or last incoming value is the intended winner.

The source's output order is indeterminate by default. Even an application that happens to observe one input order cannot treat that observation as the statement's reconciliation rule. The documentation permits a source query to impose ordering where needed, including to avoid deadlocks, but ordering does not turn repeated modification of one target into a supported merge strategy.

I like that the cardinality requirement makes an assumption visible. A reconciliation process often already depends on one authoritative source row per target identity. MERGE places that assumption directly beside the join that needs it.

MERGE and ON CONFLICT have different guarantees

The transaction isolation documentation at the release tag explicitly distinguishes MERGE from INSERT ... ON CONFLICT DO UPDATE. At Read Committed isolation, the latter guarantees an insert or update for each proposed row unless an unrelated error intervenes. MERGE with insert and update actions does not offer that same guarantee.

If MERGE attempts an insert and another transaction concurrently inserts a duplicate key, a unique index can produce a uniqueness error. PostgreSQL does not restart the merge by reclassifying that attempted insertion through the matched clauses. The fact that an update action appears in the same statement does not make it the automatic recovery path for the competing insertion.

Concurrent changes to an existing target have another consequence. If an update changes the row but it still satisfies the join, PostgreSQL can act on the updated version. It reevaluates the action conditions from the first action. A branch selected before waiting need not remain the branch selected against the new row version.

This behavior matters when action conditions depend on target state. The conditions describe what is appropriate for the row version PostgreSQL can modify under the isolation rules. They do not reserve the earlier observed state merely because the statement has several possible actions.

The appropriate choice between commands therefore depends on the operation being expressed. MERGE supplies source-driven reconciliation with conditional actions. ON CONFLICT supplies its documented response to insertion conflicts. Substituting one for the other based only on a similar-looking insert-and-update example loses the distinction that the isolation documentation makes explicit.

Privileges and triggers follow the declared actions

There is no separate merge privilege. An update action needs the relevant update rights, and insertion or deletion needs the corresponding rights on the target. PostgreSQL checks the required privileges at statement start even when a particular action ends up processing no row. Reading source data and target columns used in conditions also requires the documented select rights.

Triggers have a related distinction between declaration and execution. Statement-level triggers for specified event types can fire even if no candidate takes that action. Row-level triggers fire for the event actually executed. A statement with both insert and update actions can therefore produce statement-trigger behavior for both while only updating individual rows.

The initial implementation also has no RETURNING clause. A client cannot assume that replacing an insert or update with MERGE preserves an existing result-row interface. The command returns a tag containing the total number of changed rows, combining inserted, updated, and deleted rows.

The target also has documented restrictions. It cannot be a materialized view or a foreign table, and a target with rules is unsupported. Combining conditional actions does not remove those restrictions or redirect individual actions toward different target tables. All actions belong to the target named by the statement. Triggers may cause changes elsewhere, but that is distinct from giving each merge action its own target. This matters when translating a procedural reconciliation routine that writes several relations. Such a routine cannot become one merge merely because its individual operations happen to be inserts and updates.

These rules give the new command a defined place in PostgreSQL application code. Its source relation supplies candidates, the join establishes correspondence, and ordered conditions determine each action. The caller still needs the privileges and client behavior appropriate to the complete statement, including branches that a particular batch does not use.