merge(7) - BetterMan

—

NAME

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

SYNOPSIS

bash

[ WITH \nwith_query\n [, ...] ]
MERGE INTO [ ONLY ] \ntarget_table_name\n [ * ] [ [ AS ] \ntarget_alias\n ]
\n
    USING \ndata_source\n ON \njoin_condition\n
\n
    \nwhen_clause\n [...]
\n
    [ RETURNING { * | \noutput_expression\n [ [ AS ] \noutput_name\n ] } [, ...] ]
where \ndata_source\n is:
\n
    { [ ONLY ] \nsource_table_name\n [ * ] | ( \nsource_query\n ) } [ [ AS ] \nsource_alias\n ]
and \nwhen_clause\n is:
\n
    { WHEN MATCHED [ AND \ncondition\n ] THEN { \nmerge_update\n | \nmerge_delete\n | DO NOTHING } |
\n
      WHEN NOT MATCHED BY SOURCE [ AND \ncondition\n ] THEN { \nmerge_update\n | \nmerge_delete\n | DO NOTHING } |
\n
      WHEN NOT MATCHED [ BY TARGET ] [ AND \ncondition\n ] THEN { \nmerge_insert\n | DO NOTHING } }
and \nmerge_insert\n is:
\n
    INSERT [( \ncolumn_name\n [, ...] )]
\n
        [ OVERRIDING { SYSTEM | USER } VALUE ]
\n
        { VALUES ( { \nexpression\n | DEFAULT } [, ...] ) | DEFAULT VALUES }
and \nmerge_update\n is:
\n
    UPDATE SET { \ncolumn_name\n = { \nexpression\n | DEFAULT } |
\n
                 ( \ncolumn_name\n [, ...] ) = [ ROW ] ( { \nexpression\n | DEFAULT } [, ...] ) |
\n
                 ( \ncolumn_name\n [, ...] ) = ( \nsub-SELECT\n )
\n
               } [, ...]
and \nmerge_delete\n is:
\n
    DELETE

MERGE performs actions that modify rows in the target table identified as 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 join from data_source to the target table producing zero or more candidate change rows. For each candidate change row, the status of MATCHED, NOT MATCHED BY SOURCE, or NOT MATCHED [BY TARGET] is set just once, after which WHEN clauses are evaluated in the order specified. For each candidate change row, the first clause to evaluate as true is executed. No more than one WHEN clause is executed 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 table name is specified. All actions refer to the target table, though modifications to other tables may be made using triggers.

When DO 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 optional RETURNING clause causes MERGE 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 the merge_action() function can be computed. When an INSERT or UPDATE action is performed, the new values of the target table's columns are used. When a DELETE is performed, the old values of the target table's columns are used. The syntax of the RETURNING list is identical to that of the output list of SELECT.

There is no separate MERGE privilege. If you specify an update action, you must have the UPDATE privilege on the column(s) of the target table that are referred to in the SET clause. If you specify an insert action, you must have the INSERT privilege on the target table. If you specify a delete action, you must have the DELETE privilege on the target table. If you specify a DO NOTHING action, you must have the SELECT privilege on at least one column of the target table. You will also need SELECT privilege on any column(s) of the data_source and of the target table referred to in any condition (including join_condition) or expression. Privileges are tested once at statement start and are checked whether or not particular WHEN 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

with_query

target_table_name

If target_table_name is a view, it must either be automatically updatable with no INSTEAD OF triggers, or it must have INSTEAD OF triggers for every type of action (INSERT, UPDATE, and DELETE) specified in the WHEN clauses. Views with rules are not supported.

target_alias

source_table_name

source_query

source_alias

join_condition

If both WHEN NOT MATCHED BY SOURCE and WHEN NOT MATCHED [BY TARGET] clauses are specified, the MERGE command will perform a FULL join between data_source and the target table. For this to work, at least one join_condition subexpression must use an operator that can support a hash join, or all of the subexpressions must use operators that can support a merge join.

when_clause

The WHEN clause may specify WHEN MATCHED, WHEN NOT MATCHED BY SOURCE, or WHEN NOT MATCHED [BY TARGET]. Note that the SQL standard only defines WHEN MATCHED and WHEN NOT MATCHED (which is defined to mean no matching target row). WHEN NOT MATCHED BY SOURCE is an extension to the SQL standard, as is the option to append BY TARGET to WHEN NOT MATCHED, to make its meaning more explicit.

If the WHEN clause specifies WHEN MATCHED and the candidate change row matches a row in the data_source to a row in the target table, the WHEN clause is executed if the condition is absent or it evaluates to true.

If the WHEN clause specifies WHEN NOT MATCHED BY SOURCE and the candidate change row represents a row in the target table that does not match a row in the data_source, the WHEN clause is executed if the condition is absent or it evaluates to true.

If the WHEN clause specifies WHEN NOT MATCHED [BY TARGET] and the candidate change row represents a row in the data_source that does not match a row in the target table, the WHEN clause is executed if the condition is absent or it evaluates to true.

condition

A condition on a WHEN MATCHED clause can refer to columns in both the source and the target relations. A condition on a WHEN NOT MATCHED BY SOURCE clause can only refer to columns from the target relation, since by definition there is no matching source row. A condition on a WHEN NOT MATCHED [BY TARGET] 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_insert

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 one VALUES clause can be specified. The VALUES clause can only refer to columns from the source relation, since by definition there is no matching target row.

merge_update

Neither a table name nor a WHERE clause are allowed.

merge_delete

column_name

OVERRIDING SYSTEM VALUE

OVERRIDING USER VALUE

DEFAULT VALUES

expression

DEFAULT

sub-SELECT

output_expression

Writing * will return all columns from the source table, followed by all columns from the target table. Often this will lead to a lot of duplication, since it is common for the source and target tables to have a lot of the same columns. This can be avoided by qualifying the * with the name or alias of the source or target table.

output_name

OUTPUTS

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

bash

MERGE \ntotal_count

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

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

NOTES

The following steps take place during the execution of MERGE.

If the target relation is a view with INSTEAD OF ROW triggers for the action's event type, they are used to perform the action instead.

In summary, statement triggers for an event type (say, INSERT) will be fired whenever we specify an action of that kind. In contrast, row-level triggers will fire only for the specific event type being executed. So a MERGE command might fire statement triggers for both UPDATE and INSERT, even though only UPDATE 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 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 the same row are simply ignored.

If a WHEN clause omits an AND sub-clause, it becomes the final reachable clause of that kind (MATCHED, NOT MATCHED BY SOURCE, or NOT MATCHED [BY TARGET]). If a later WHEN 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. A source_query can be used to specify a consistent ordering, if required, which might be needed to avoid deadlocks between concurrent transactions.

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

EXAMPLES

Perform maintenance on customer_accounts based upon new recent_transactions.

bash

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
\n
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
\n
  INSERT (customer_id, balance)
\n
  VALUES (t.customer_id, t.transaction_value);

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

bash

MERGE INTO customer_account ca
USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
\n
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
\n
  INSERT (customer_id, balance)
\n
  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.

bash

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

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

Update wines 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.

bash

MERGE INTO wines w
USING new_wine_list s
ON s.winename = w.winename
WHEN NOT MATCHED BY TARGET THEN
\n
  INSERT VALUES(s.winename, s.stock)
WHEN MATCHED AND w.stock != s.stock THEN
\n
  UPDATE SET stock = s.stock
WHEN NOT MATCHED BY SOURCE THEN
\n
  DELETE;

COMPATIBILITY

This command conforms to the SQL standard.

The WITH clause, BY SOURCE and BY TARGET qualifiers to WHEN NOT MATCHED, DO NOTHING action, and RETURNING clause are extensions to the SQL standard.