Movatterモバイル変換

MERGE

MERGE — conditionally insert, update, or delete rows of a table

Synopsis

[ WITHwith_query [, ...] ]MERGE INTO [ ONLY ]target_table_name [ * ] [ [ AS ]target_alias ]    USINGdata_source ONjoin_conditionwhen_clause [...]    [ RETURNING { * |output_expression [ [ AS ]output_name ] } [, ...] ]wheredata_source is:    { [ ONLY ]source_table_name [ * ] | (source_query ) } [ [ AS ]source_alias ]andwhen_clause is:    { WHEN MATCHED [ ANDcondition ] THEN {merge_update |merge_delete | DO NOTHING } |      WHEN NOT MATCHED BY SOURCE [ ANDcondition ] THEN {merge_update |merge_delete | DO NOTHING } |      WHEN NOT MATCHED [ BY TARGET ] [ ANDcondition ] THEN {merge_insert | DO NOTHING } }andmerge_insert is:    INSERT [(column_name [, ...] )]        [ OVERRIDING { SYSTEM | USER } VALUE ]        { VALUES ( {expression | DEFAULT } [, ...] ) | DEFAULT VALUES }andmerge_update is:    UPDATE SET {column_name = {expression | DEFAULT } |                 (column_name [, ...] ) = [ ROW ] ( {expression | DEFAULT } [, ...] ) |                 (column_name [, ...] ) = (sub-SELECT )               } [, ...]andmerge_delete is:    DELETE

Description

MERGE performs actions that modify rows in the target table identified astarget_table_name, using thedata_source.MERGE provides a singleSQL statement that can conditionallyINSERT,UPDATE orDELETE rows, a task that would otherwise require multiple procedural language statements.

First, theMERGE command performs a join fromdata_source to the target table producing zero or more candidate change rows. For each candidate change row, the status ofMATCHED,NOT MATCHED BY SOURCE, orNOT MATCHED [BY TARGET] is set just once, after whichWHEN clauses are evaluated in the order specified. For each candidate change row, the first clause to evaluate as true is executed. No more than oneWHEN clause is executed for any candidate change row.

MERGE actions have the same effect as regularUPDATE,INSERT, orDELETE commands of the same names. The syntax of those commands is different, notably that there is noWHERE clause and no table name is specified. All actions refer to the target table, though modifications to other tables may be made using triggers.

WhenDO NOTHING is specified, the source row is skipped. Since actions are evaluated in their specified order,DO NOTHING can be handy to skip non-interesting source rows before more fine-grained handling.

The optionalRETURNING clause causesMERGE to compute and return value(s) based on each row inserted, updated, or deleted. Any expression using the source or target table's columns, or themerge_action() function can be computed. When anINSERT orUPDATE action is performed, the new values of the target table's columns are used. When aDELETE is performed, the old values of the target table's columns are used. The syntax of theRETURNING list is identical to that of the output list ofSELECT.

There is no separateMERGE privilege. If you specify an update action, you must have theUPDATE privilege on the column(s) of the target table that are referred to in theSET clause. If you specify an insert action, you must have theINSERT privilege on the target table. If you specify a delete action, you must have theDELETE privilege on the target table. If you specify aDO NOTHING action, you must have theSELECT privilege on at least one column of the target table. You will also needSELECT privilege on any column(s) of thedata_source and of the target table referred to in anycondition (includingjoin_condition) orexpression. Privileges are tested once at statement start and are checked whether or not particularWHEN clauses are executed.

MERGE is not supported if the target table is a materialized view, foreign table, or if it has any rules defined on it.

Parameters

Outputs

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

MERGEtotal_count

Thetotal_count is the total number of rows changed (whether inserted, updated, or deleted). Iftotal_count is 0, no rows were changed in any way.

If theMERGE command contains aRETURNING clause, the result will be similar to that of aSELECT statement containing the columns and values defined in theRETURNING list, computed over the row(s) inserted, updated, or deleted by the command.

Notes

The following steps take place during the execution ofMERGE.

In summary, statement triggers for an event type (say,INSERT) will be fired whenever wespecify an action of that kind. In contrast, row-level triggers will fire only for the specific event type beingexecuted. So aMERGE command might fire statement triggers for bothUPDATE andINSERT, even though onlyUPDATE row triggers were fired.

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 the row will cause an error. This can also occur if row triggers make changes to the target table and the rows so modified are then subsequently also modified byMERGE. If the repeated action is anINSERT, this will cause a uniqueness violation, while a repeatedUPDATE orDELETE will cause a cardinality violation; the latter behavior is required by theSQL standard. This differs from historicalPostgreSQL behavior of joins inUPDATE andDELETE statements where second and subsequent attempts to modify the same row are simply ignored.

If aWHEN clause omits anAND sub-clause, it becomes the final reachable clause of that kind (MATCHED,NOT MATCHED BY SOURCE, orNOT MATCHED [BY TARGET]). If a laterWHEN clause of that kind is specified it would be provably unreachable and an error is raised. If no final reachable clause is specified of either kind, it is possible that no action will be taken for a candidate change row.

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

WhenMERGE is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; seeSection 13.2 for an explanation on the behavior at each isolation level. You may also wish to consider usingINSERT ... ON CONFLICT as an alternative statement which offers the ability to run anUPDATE if a concurrentINSERT occurs. There are a variety of differences and restrictions between the two statement types and they are not interchangeable.

Examples

Perform maintenance oncustomer_accounts based upon newrecent_transactions.

MERGE INTO customer_account caUSING recent_transactions tON t.customer_id = ca.customer_idWHEN MATCHED THEN  UPDATE SET balance = balance + transaction_valueWHEN NOT MATCHED THEN  INSERT (customer_id, balance)  VALUES (t.customer_id, t.transaction_value);

Notice that this would be exactly equivalent to the following statement because theMATCHED result does not change during execution.

MERGE INTO customer_account caUSING (SELECT customer_id, transaction_value FROM recent_transactions) AS tON t.customer_id = ca.customer_idWHEN MATCHED THEN  UPDATE SET balance = balance + transaction_valueWHEN NOT MATCHED THEN  INSERT (customer_id, balance)  VALUES (t.customer_id, t.transaction_value);

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. Return details of all changes made.

MERGE INTO wines wUSING wine_stock_changes sON s.winename = w.winenameWHEN 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_deltaWHEN MATCHED THEN  DELETERETURNING merge_action(), w.*;

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

Updatewines based on a replacement wine list, inserting rows for any new stock, updating modified stock entries, and deleting any wines not present in the new list.

MERGE INTO wines wUSING new_wine_list sON s.winename = w.winenameWHEN NOT MATCHED BY TARGET THEN  INSERT VALUES(s.winename, s.stock)WHEN MATCHED AND w.stock != s.stock THEN  UPDATE SET stock = s.stockWHEN NOT MATCHED BY SOURCE THEN  DELETE;

Compatibility

This command conforms to theSQL standard.

TheWITH clause,BY SOURCE andBY TARGET qualifiers toWHEN NOT MATCHED,DO NOTHING action, andRETURNING clause are extensions to theSQL standard.