Description

MERGE performs actions that modify rows in the target_table_name, using the data_source. MERGE provides a single SQL statement that can conditionally INSERT, UPDATE or DELETE rows, a task that would otherwise require multiple procedural language statements.

First, the MERGE command performs a left outer join from data_source to target_table_name producing zero or more candidate change rows. For each candidate change row, WHEN clauses are evaluated in the order specified; if one of them is activated, the specified action occurs. No more than one WHEN clause can be activated for any candidate change row.

MERGE actions have the same effect as regular UPDATE, INSERT, or DELETE commands of the same names. The syntax of those commands is different, notably that there is no WHERE clause and no tablename is specified. All actions refer to the target_table_name, though modifications to other tables may be made using triggers.

The ON clause must join on all of the column(s) of the primary key, or if other columns are specified then another unique index on the target_table_name. Each candidate change row is locked via speculative insertion into the chosen unique index, ensuring that the status of MATCHED or NOT MATCHED is decided just once for each candidate change row, preventing interference from concurrent transactions. As a result of these requirements MERGE cannot yet work against partitioned tables.

There is no MERGE privilege. You must have the UPDATE privilege on the column(s) of the target_table_name referred to in the SET clause if you specify an update action, the INSERT privilege on the target_table_name if you specify an insert action and/or the DELETE privilege on the if you specify a delete action on the target_table_name. Privileges are tested once at statement start and are checked whether or not particular WHEN clauses are activated during the subsequent execution. You will require the SELECT privilege on the data_source and any column(s) of the target_table_name referred to in a condition.

Parameters

Outputs

On successful completion, a MERGE command returns a command tag of the form

MERGE total-count

The total-count is the total number of rows changed (whether updated, inserted or deleted). If total-count is 0, no rows were changed in any way.

Notes

The following steps take place during the execution of MERGE.

In summary, statement triggers for an event type (say, INSERT) will be fired whenever we specify an action of that kind. Row-level triggers will fire only for the one event type activated. So a MERGE might fire statement triggers for both UPDATE and INSERT, even though only UPDATE row triggers were fired.

The order in which rows are generated from the data source is indeterminate by default. A source_query can be used to specify a consistent ordering, if required, which might be needed to avoid deadlocks between concurrent transactions.

You should ensure that the join produces at most one candidate change row for each target row. In other words, a target row shouldn't join to more than one data source row. If it does, then only one of the candidate change rows will be used to modify the target row, later attempts to modify will cause an error. This can also occur if row triggers make changes to the target table which are then subsequently modified by MERGE. If the repeated action is an INSERT this will cause a uniqueness violation while a repeated UPDATE or DELETE will cause a cardinality violation; the latter behavior is required by the SQL Standard. This differs from historical PostgreSQL behavior of joins in UPDATE and DELETE statements where second and subsequent attempts to modify are simply ignored.

If the ON clause is a constant expression that evaluates to false then no join takes place and the source is used directly as candidate change rows.

If a WHEN clause omits an AND clause it becomes the final reachable clause of that kind (MATCHED or NOT MATCHED). If a later WHEN clause of that kind is specified it would be provably unreachable and an error is raised.

There is no RETURNING clause with MERGE. Actions of INSERT, UPDATE and DELETE cannot contain RETURNING or WITH clauses.

Examples

Perform maintenance on CustomerAccounts based upon new Transactions.

MERGE CustomerAccount CA
USING RecentTransactions T
ON T.CustomerId = CA.CustomerId
WHEN MATCHED THEN
  UPDATE SET Balance = Balance + TransactionValue
WHEN NOT MATCHED THEN
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionValue)
;

notice that this would be exactly equivalent to the following statement because the MATCHED result does not change during execution

MERGE CustomerAccount CA
USING (Select CustomerId, TransactionValue From RecentTransactions) AS T
ON CA.CustomerId = T.CustomerId
WHEN NOT MATCHED THEN
  INSERT (CustomerId, Balance)
  VALUES (T.CustomerId, T.TransactionValue)
WHEN MATCHED THEN
  UPDATE SET Balance = Balance + TransactionValue
;

Attempt to insert a new stock item along with the quantity of stock. If the item already exists, instead update the stock count of the existing item. Don't allow entries that have zero stock.

MERGE INTO wines w
USING wine_stock_changes s
ON s.winename = w.winename
WHEN NOT MATCHED AND s.stock_delta > 0 THEN
  INSERT VALUES(s.winename, s.stock_delta)
WHEN MATCHED AND w.stock + s.stock_delta > 0 THEN
  UPDATE SET stock = w.stock + s.stock_delta;
ELSE
  DELETE
;

The wine_stock_changes table might be, for example, a temporary table recently loaded into the database.

Compatibility

This command conforms to the SQL standard, except that the DO NOTHING clause is a PostgreSQL extension.