MERGE

7

SQL - Language Statements

MERGE

conditionally insert, update, or delete rows of a table

\[ WITH

with\_query

\[, \...\] \] MERGE INTO

target\_table\_name

\[ \[ AS \]

target\_alias

\] USING

data\_source

ON

join\_condition

when\_clause

\[\...\]

where

data\_source

is

{

source\_table\_name

\| (

source\_query

) } \[ \[ AS \]

source\_alias

\]

and

when\_clause

is

{ WHEN MATCHED \[ AND

condition

\] THEN {

merge\_update

\|

merge\_delete

\| DO NOTHING } \| WHEN NOT MATCHED \[ AND

condition

\] THEN {

merge\_insert

\| DO NOTHING } }

and

merge\_insert

is

INSERT \[(

column\_name

\[, \...\] )\] \[ OVERRIDING { SYSTEM \| USER } VALUE \] { VALUES ( {

expression

\| DEFAULT } \[, \...\] ) \| DEFAULT VALUES }

and

merge\_update

is

UPDATE SET {

column\_name

= {

expression

\| DEFAULT } \| (

column\_name

\[, \...\] ) = ( {

expression

\| DEFAULT } \[, \...\] ) } \[, \...\]

and

merge\_delete

is

DELETE

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 join from data\_source to
target\_table\_name producing zero or more candidate change rows. For
each candidate change row, the status of `MATCHED` or `NOT MATCHED` 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\_name, 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.

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\_name that are referred to in the `SET` clause. If you
specify an insert action, you must have the `INSERT` privilege on the
target\_table\_name. If you specify an delete action, you must have the
`DELETE` privilege on the target\_table\_name. Privileges are tested
once at statement start and are checked whether or not particular `WHEN`
clauses are executed. You will require the `SELECT` privilege on the
data\_source and any column(s) of the target\_table\_name referred to in
a `condition`.

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

Parameters
==========

target\_table\_name

:   The name (optionally schema-qualified) of the target table to merge
    into.

target\_alias

:   A substitute name for the target table. When an alias is provided,
    it completely hides the actual name of the table. For example, given
    `MERGE INTO foo AS f`, the remainder of the `MERGE` statement must
    refer to this table as `f` not `foo`.

source\_table\_name

:   The name (optionally schema-qualified) of the source table, view, or
    transition table.

source\_query

:   A query (`SELECT` statement or `VALUES` statement) that supplies the
    rows to be merged into the target\_table\_name. Refer to the
    [???](#sql-select) statement or [???](#sql-values) statement for a
    description of the syntax.

source\_alias

:   A 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\_condition

:   join\_condition is an expression resulting in a value of type
    `boolean` (similar to a `WHERE` clause) that specifies which rows in
    the data\_source match rows in the target\_table\_name.

    > **Warning**
    >
    > Only columns from target\_table\_name that attempt to match
    > data\_source rows should appear in join\_condition.
    > join\_condition subexpressions that only reference
    > target\_table\_name columns can affect which action is taken,
    > often in surprising ways.

when\_clause

:   At least one `WHEN` clause is required.

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

    Conversely, if the `WHEN` clause specifies `WHEN NOT MATCHED` and
    the candidate change row does not match a row in the
    target\_table\_name, the `WHEN` clause is executed if the condition
    is absent or it evaluates to `true`.

condition

:   An expression that returns a value of type `boolean`. If this
    expression for a `WHEN` clause returns `true`, then the action for
    that clause is executed for that row.

    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` 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

:   The specification of an `INSERT` 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 expression for any column is not of the correct data type,
    automatic type conversion will be attempted.

    If target\_table\_name is a partitioned table, each row is routed to
    the appropriate partition and inserted into it. If
    target\_table\_name 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

:   The specification of an `UPDATE` action that updates the current row
    of the target\_table\_name. Column names may not be specified more
    than once.

    Neither a table name nor a `WHERE` clause are allowed.

merge\_delete

:   Specifies a `DELETE` action that deletes the current row of the
    target\_table\_name. Do not include the table name or any other
    clauses, as you would normally do with a [???](#sql-delete) command.

column\_name

:   The name of a column in the target\_table\_name. 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 VALUE`

:   Without this clause, it is an error to specify an explicit value
    (other than `DEFAULT`) for an identity column defined as
    `GENERATED ALWAYS`. This clause overrides that restriction.

`OVERRIDING USER VALUE`

:   If this clause is specified, then any values supplied for identity
    columns defined as `GENERATED BY DEFAULT` are ignored and the
    default sequence-generated values are applied.

`DEFAULT VALUES`

:   All columns will be filled with their default values. (An
    `OVERRIDING` clause is not permitted in this form.)

expression

:   An expression to assign to the column. If used in a `WHEN MATCHED`
    clause, the expression can use values from the original row in the
    target table, and values from the `data_source` row. If used in a
    `WHEN NOT MATCHED` clause, the expression can use values from the
    `data_source`.

`DEFAULT`

:   Set the column to its default value (which will be `NULL` if no
    specific default expression has been assigned to it).

with\_query

:   The `WITH` clause allows you to specify one or more subqueries that
    can be referenced by name in the `MERGE` query. See
    [???](#queries-with) and [???](#sql-select) for details.

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 inserted,
updated, 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`.

1.  Perform any `BEFORE STATEMENT` triggers for all actions specified,
    whether or not their `WHEN` clauses match.

2.  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,

    1.  Evaluate whether each row is `MATCHED` or `NOT MATCHED`.

    2.  Test each `WHEN` condition in the order specified until one
        returns true.

    3.  When a condition returns true, perform the following actions:

        1.  Perform any `BEFORE ROW` triggers that fire for the
            action\'s event type.

        2.  Perform the specified action, invoking any check constraints
            on the target table.

        3.  Perform any `AFTER ROW` triggers that fire for the action\'s
            event type.

3.  Perform any `AFTER STATEMENT` triggers for actions specified,
    whether or not they actually occur. This is similar to the behavior
    of an `UPDATE` statement that modifies no rows.

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` or `NOT MATCHED`). 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.

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

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 `CustomerAccounts` based upon new `Transactions`.

    MERGE INTO 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 INTO 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
    WHEN MATCHED THEN
      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.

The WITH clause and `DO NOTHING` action are extensions to the SQL
standard.
