MERGE — conditionally insert, update, or delete rows of a table
[ WITHwith_query[, ...] ]MERGE INTO [ ONLY ]target_table_name[ * ] [ [ AS ]target_alias] USINGdata_sourceONjoin_conditionwhen_clause[...]wheredata_sourceis: { [ ONLY ]source_table_name[ * ] | (source_query) } [ [ AS ]source_alias]andwhen_clauseis: { WHEN MATCHED [ ANDcondition] THEN {merge_update|merge_delete| DO NOTHING } | WHEN NOT MATCHED [ ANDcondition] THEN {merge_insert| DO NOTHING } }andmerge_insertis: INSERT [(column_name[, ...] )] [ OVERRIDING { SYSTEM | USER } VALUE ] { VALUES ( {expression| DEFAULT } [, ...] ) | DEFAULT VALUES }andmerge_updateis: UPDATE SET {column_name= {expression| DEFAULT } | (column_name[, ...] ) = [ ROW ] ( {expression| DEFAULT } [, ...] ) | (column_name[, ...] ) = (sub-SELECT) } [, ...]andmerge_deleteis: DELETE
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 orNOT MATCHED 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.
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.
with_queryTheWITH clause allows you to specify one or more subqueries that can be referenced by name in theMERGE query. SeeSection 7.8 andSELECT for details. Note thatWITH RECURSIVE is not supported byMERGE.
target_table_nameThe name (optionally schema-qualified) of the target table to merge into. IfONLY is specified before the table name, matching rows are updated or deleted in the named table only. IfONLY is not specified, matching rows are also updated or deleted in any tables inheriting from the named table. Optionally,* can be specified after the table name to explicitly indicate that descendant tables are included. TheONLY keyword and* option do not affect insert actions, which always insert into the named table only.
target_aliasA substitute name for the target table. When an alias is provided, it completely hides the actual name of the table. For example, givenMERGE INTO foo AS f, the remainder of theMERGE statement must refer to this table asf notfoo.
source_table_nameThe name (optionally schema-qualified) of the source table, view, or transition table. IfONLY is specified before the table name, matching rows are included from the named table only. IfONLY is not specified, matching rows are also included from any tables inheriting from the named table. Optionally,* can be specified after the table name to explicitly indicate that descendant tables are included.
source_queryA query (SELECT statement orVALUES statement) that supplies the rows to be merged into the target table. Refer to theSELECT statement orVALUES statement for a description of the syntax.
source_aliasA substitute name for the data source. When an alias is provided, it completely hides the actual name of the table or the fact that a query was issued.
join_conditionjoin_condition is an expression resulting in a value of typeboolean (similar to aWHERE clause) that specifies which rows in thedata_source match rows in the target table.
Only columns from the target table that attempt to matchdata_source rows should appear injoin_condition.join_condition subexpressions that only reference the target table's columns can affect which action is taken, often in surprising ways.
when_clauseAt least oneWHEN clause is required.
If theWHEN clause specifiesWHEN MATCHED and the candidate change row matches a row in the target table, theWHEN clause is executed if thecondition is absent or it evaluates totrue.
Conversely, if theWHEN clause specifiesWHEN NOT MATCHED and the candidate change row does not match a row in the target table, theWHEN clause is executed if thecondition is absent or it evaluates totrue.
conditionAn expression that returns a value of typeboolean. If this expression for aWHEN clause returnstrue, then the action for that clause is executed for that row.
A condition on aWHEN MATCHED clause can refer to columns in both the source and the target relations. A condition on aWHEN NOT MATCHED clause can only refer to columns from the source relation, since by definition there is no matching target row. Only the system attributes from the target table are accessible.
merge_insertThe specification of anINSERT action that inserts one row into the target table. The target column names can be listed in any order. If no list of column names is given at all, the default is all the columns of the table in their declared order.
Each column not present in the explicit or implicit column list will be filled with a default value, either its declared default value or null if there is none.
If the target table is a partitioned table, each row is routed to the appropriate partition and inserted into it. If the target table is a partition, an error will occur if any input row violates the partition constraint.
Column names may not be specified more than once.INSERT actions cannot contain sub-selects.
Only oneVALUES clause can be specified. TheVALUES clause can only refer to columns from the source relation, since by definition there is no matching target row.
merge_updateThe specification of anUPDATE action that updates the current row of the target table. Column names may not be specified more than once.
Neither a table name nor aWHERE clause are allowed.
merge_deleteSpecifies aDELETE action that deletes the current row of the target table. Do not include the table name or any other clauses, as you would normally do with aDELETE command.
column_nameThe name of a column in the target table. The column name can be qualified with a subfield name or array subscript, if needed. (Inserting into only some fields of a composite column leaves the other fields null.) Do not include the table's name in the specification of a target column.
OVERRIDING SYSTEM VALUEWithout this clause, it is an error to specify an explicit value (other thanDEFAULT) for an identity column defined asGENERATED ALWAYS. This clause overrides that restriction.
OVERRIDING USER VALUEIf this clause is specified, then any values supplied for identity columns defined asGENERATED BY DEFAULT are ignored and the default sequence-generated values are applied.
DEFAULT VALUESAll columns will be filled with their default values. (AnOVERRIDING clause is not permitted in this form.)
expressionAn expression to assign to the column. If used in aWHEN MATCHED clause, the expression can use values from the original row in the target table, and values from thedata_source row. If used in aWHEN NOT MATCHED clause, the expression can use values from thedata_source row.
DEFAULTSet the column to its default value (which will beNULL if no specific default expression has been assigned to it).
sub-SELECTASELECT sub-query that produces as many output columns as are listed in the parenthesized column list preceding it. The sub-query must yield no more than one row when executed. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns. The sub-query can refer to values from the original row in the target table, and values from thedata_source row.
On successful completion, aMERGE command returns a command tag of the form
MERGEtotal_countThetotal_count is the total number of rows changed (whether inserted, updated, or deleted). Iftotal_count is 0, no rows were changed in any way.
The following steps take place during the execution ofMERGE.
Perform anyBEFORE STATEMENT triggers for all actions specified, whether or not theirWHEN clauses match.
Perform a join from source to target table. The resulting query will be optimized normally and will produce a set of candidate change rows. For each candidate change row,
Evaluate whether each row isMATCHED orNOT MATCHED.
Test eachWHEN condition in the order specified until one returns true.
When a condition returns true, perform the following actions:
Perform anyBEFORE ROW triggers that fire for the action's event type.
Perform the specified action, invoking any check constraints on the target table.
Perform anyAFTER ROW triggers that fire for the action's event type.
Perform anyAFTER STATEMENT triggers for actions specified, whether or not they actually occur. This is similar to the behavior of anUPDATE statement that modifies no rows.
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 orNOT MATCHED). 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.
There is noRETURNING clause withMERGE. Actions ofINSERT,UPDATE andDELETE cannot containRETURNING orWITH clauses.
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.
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.
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 DELETE;
Thewine_stock_changes table might be, for example, a temporary table recently loaded into the database.
This command conforms to theSQL standard.
TheWITH clause andDO NOTHING action are extensions to theSQL standard.
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please usethis form to report a documentation issue.