path: root/doc/src/sgml/man7/MERGE.7

'\" t
.\"     Title: MERGE
.\"    Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\"      Date: 2024
.\"    Manual: PostgreSQL 16.3 Documentation
.\"    Source: PostgreSQL 16.3
.\"  Language: English
.\"
.TH "MERGE" "7" "2024" "PostgreSQL 16.3" "PostgreSQL 16.3 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.\" http://bugs.debian.org/507673
.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html
.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
.ie \n(.g .ds Aq \(aq
.el       .ds Aq '
.\" -----------------------------------------------------------------
.\" * set default formatting
.\" -----------------------------------------------------------------
.\" disable hyphenation
.nh
.\" disable justification (adjust text to left margin only)
.ad l
.\" -----------------------------------------------------------------
.\" * MAIN CONTENT STARTS HERE *
.\" -----------------------------------------------------------------
.SH "NAME"
MERGE \- conditionally insert, update, or delete rows of a table
.SH "SYNOPSIS"
.sp
.nf
[ WITH \fIwith_query\fR [, \&.\&.\&.] ]
MERGE INTO [ ONLY ] \fItarget_table_name\fR [ * ] [ [ AS ] \fItarget_alias\fR ]
USING \fIdata_source\fR ON \fIjoin_condition\fR
\fIwhen_clause\fR [\&.\&.\&.]

where \fIdata_source\fR is:

{ [ ONLY ] \fIsource_table_name\fR [ * ] | ( \fIsource_query\fR ) } [ [ AS ] \fIsource_alias\fR ]

and \fIwhen_clause\fR is:

{ WHEN MATCHED [ AND \fIcondition\fR ] THEN { \fImerge_update\fR | \fImerge_delete\fR | DO NOTHING } |
  WHEN NOT MATCHED [ AND \fIcondition\fR ] THEN { \fImerge_insert\fR | DO NOTHING } }

and \fImerge_insert\fR is:

INSERT [( \fIcolumn_name\fR [, \&.\&.\&.] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { \fIexpression\fR | DEFAULT } [, \&.\&.\&.] ) | DEFAULT VALUES }

and \fImerge_update\fR is:

UPDATE SET { \fIcolumn_name\fR = { \fIexpression\fR | DEFAULT } |
             ( \fIcolumn_name\fR [, \&.\&.\&.] ) = [ ROW ] ( { \fIexpression\fR | DEFAULT } [, \&.\&.\&.] ) |
             ( \fIcolumn_name\fR [, \&.\&.\&.] ) = ( \fIsub\-SELECT\fR )
           } [, \&.\&.\&.]

and \fImerge_delete\fR is:

DELETE
.fi
.SH "DESCRIPTION"
.PP
\fBMERGE\fR
performs actions that modify rows in the target table identified as
\fItarget_table_name\fR, using the
\fIdata_source\fR\&.
\fBMERGE\fR
provides a single
SQL
statement that can conditionally
\fBINSERT\fR,
\fBUPDATE\fR
or
\fBDELETE\fR
rows, a task that would otherwise require multiple procedural language statements\&.
.PP
First, the
\fBMERGE\fR
command performs a join from
\fIdata_source\fR
to the target table 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\&.
.PP
\fBMERGE\fR
actions have the same effect as regular
\fBUPDATE\fR,
\fBINSERT\fR, or
\fBDELETE\fR
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\&.
.PP
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\&.
.PP
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
\fIdata_source\fR
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\&.
.PP
\fBMERGE\fR
is not supported if the target table is a materialized view, foreign table, or if it has any rules defined on it\&.
.SH "PARAMETERS"
.PP
\fIwith_query\fR
.RS 4
The
WITH
clause allows you to specify one or more subqueries that can be referenced by name in the
\fBMERGE\fR
query\&. See
Section\ \&7.8
and
\fBSELECT\fR(7)
for details\&. Note that
WITH RECURSIVE
is not supported by
\fBMERGE\fR\&.
.RE
.PP
\fItarget_table_name\fR
.RS 4
The name (optionally schema\-qualified) of the target table to merge into\&. If
ONLY
is specified before the table name, matching rows are updated or deleted in the named table only\&. If
ONLY
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\&. The
ONLY
keyword and
*
option do not affect insert actions, which always insert into the named table only\&.
.RE
.PP
\fItarget_alias\fR
.RS 4
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
\fBMERGE\fR
statement must refer to this table as
f
not
foo\&.
.RE
.PP
\fIsource_table_name\fR
.RS 4
The name (optionally schema\-qualified) of the source table, view, or transition table\&. If
ONLY
is specified before the table name, matching rows are included from the named table only\&. If
ONLY
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\&.
.RE
.PP
\fIsource_query\fR
.RS 4
A query (\fBSELECT\fR
statement or
\fBVALUES\fR
statement) that supplies the rows to be merged into the target table\&. Refer to the
\fBSELECT\fR(7)
statement or
\fBVALUES\fR(7)
statement for a description of the syntax\&.
.RE
.PP
\fIsource_alias\fR
.RS 4
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\&.
.RE
.PP
\fIjoin_condition\fR
.RS 4
\fIjoin_condition\fR
is an expression resulting in a value of type
boolean
(similar to a
WHERE
clause) that specifies which rows in the
\fIdata_source\fR
match rows in the target table\&.
.if n \{\
.sp
.\}
.RS 4
.it 1 an-trap
.nr an-no-space-flag 1
.nr an-break-flag 1
.br
.ps +1
\fBWarning\fR
.ps -1
.br
Only columns from the target table that attempt to match
\fIdata_source\fR
rows should appear in
\fIjoin_condition\fR\&.
\fIjoin_condition\fR
subexpressions that only reference the target table\*(Aqs columns can affect which action is taken, often in surprising ways\&.
.sp .5v
.RE
.RE
.PP
\fIwhen_clause\fR
.RS 4
At least one
WHEN
clause is required\&.
.sp
If the
WHEN
clause specifies
WHEN MATCHED
and the candidate change row matches a row in the target table, the
WHEN
clause is executed if the
\fIcondition\fR
is absent or it evaluates to
true\&.
.sp
Conversely, if the
WHEN
clause specifies
WHEN NOT MATCHED
and the candidate change row does not match a row in the target table, the
WHEN
clause is executed if the
\fIcondition\fR
is absent or it evaluates to
true\&.
.RE
.PP
\fIcondition\fR
.RS 4
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\&.
.sp
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\&.
.RE
.PP
\fImerge_insert\fR
.RS 4
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\&.
.sp
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\&.
.sp
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\&.
.sp
Column names may not be specified more than once\&.
\fBINSERT\fR
actions cannot contain sub\-selects\&.
.sp
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\&.
.RE
.PP
\fImerge_update\fR
.RS 4
The specification of an
UPDATE
action that updates the current row of the target table\&. Column names may not be specified more than once\&.
.sp
Neither a table name nor a
WHERE
clause are allowed\&.
.RE
.PP
\fImerge_delete\fR
.RS 4
Specifies a
DELETE
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 a
\fBDELETE\fR(7)
command\&.
.RE
.PP
\fIcolumn_name\fR
.RS 4
The 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\*(Aqs name in the specification of a target column\&.
.RE
.PP
OVERRIDING SYSTEM VALUE
.RS 4
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\&.
.RE
.PP
OVERRIDING USER VALUE
.RS 4
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\&.
.RE
.PP
DEFAULT VALUES
.RS 4
All columns will be filled with their default values\&. (An
OVERRIDING
clause is not permitted in this form\&.)
.RE
.PP
\fIexpression\fR
.RS 4
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
\fIdata_source\fR
row\&. If used in a
WHEN NOT MATCHED
clause, the expression can use values from the
\fIdata_source\fR
row\&.
.RE
.PP
DEFAULT
.RS 4
Set the column to its default value (which will be
NULL
if no specific default expression has been assigned to it)\&.
.RE
.PP
\fIsub\-SELECT\fR
.RS 4
A
SELECT
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 the
\fIdata_source\fR
row\&.
.RE
.SH "OUTPUTS"
.PP
On successful completion, a
\fBMERGE\fR
command returns a command tag of the form
.sp
.if n \{\
.RS 4
.\}
.nf
MERGE \fItotal_count\fR
.fi
.if n \{\
.RE
.\}
.sp
The
\fItotal_count\fR
is the total number of rows changed (whether inserted, updated, or deleted)\&. If
\fItotal_count\fR
is 0, no rows were changed in any way\&.
.SH "NOTES"
.PP
The following steps take place during the execution of
\fBMERGE\fR\&.
.sp
.RS 4
.ie n \{\
\h'-04' 1.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  1." 4.2
.\}
Perform any
BEFORE STATEMENT
triggers for all actions specified, whether or not their
WHEN
clauses match\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 2.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  2." 4.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,
.sp
.RS 4
.ie n \{\
\h'-04' 1.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  1." 4.2
.\}
Evaluate whether each row is
MATCHED
or
NOT MATCHED\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 2.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  2." 4.2
.\}
Test each
WHEN
condition in the order specified until one returns true\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 3.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  3." 4.2
.\}
When a condition returns true, perform the following actions:
.sp
.RS 4
.ie n \{\
\h'-04' 1.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  1." 4.2
.\}
Perform any
BEFORE ROW
triggers that fire for the action\*(Aqs event type\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 2.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  2." 4.2
.\}
Perform the specified action, invoking any check constraints on the target table\&.
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 3.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  3." 4.2
.\}
Perform any
AFTER ROW
triggers that fire for the action\*(Aqs event type\&.
.RE
.RE
.RE
.sp
.RS 4
.ie n \{\
\h'-04' 3.\h'+01'\c
.\}
.el \{\
.sp -1
.IP "  3." 4.2
.\}
Perform any
AFTER STATEMENT
triggers for actions specified, whether or not they actually occur\&. This is similar to the behavior of an
\fBUPDATE\fR
statement that modifies no rows\&.
.RE
.sp
In summary, statement triggers for an event type (say,
\fBINSERT\fR) will be fired whenever we
\fIspecify\fR
an action of that kind\&. In contrast, row\-level triggers will fire only for the specific event type being
\fIexecuted\fR\&. So a
\fBMERGE\fR
command might fire statement triggers for both
\fBUPDATE\fR
and
\fBINSERT\fR, even though only
\fBUPDATE\fR
row triggers were fired\&.
.PP
You should ensure that the join produces at most one candidate change row for each target row\&. In other words, a target row shouldn\*(Aqt 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
\fBMERGE\fR\&. If the repeated action is an
\fBINSERT\fR, this will cause a uniqueness violation, while a repeated
\fBUPDATE\fR
or
\fBDELETE\fR
will cause a cardinality violation; the latter behavior is required by the
SQL
standard\&. This differs from historical
PostgreSQL
behavior of joins in
\fBUPDATE\fR
and
\fBDELETE\fR
statements where second and subsequent attempts to modify the same row are simply ignored\&.
.PP
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\&.
.PP
The order in which rows are generated from the data source is indeterminate by default\&. A
\fIsource_query\fR
can be used to specify a consistent ordering, if required, which might be needed to avoid deadlocks between concurrent transactions\&.
.PP
There is no
RETURNING
clause with
\fBMERGE\fR\&. Actions of
\fBINSERT\fR,
\fBUPDATE\fR
and
\fBDELETE\fR
cannot contain
RETURNING
or
WITH
clauses\&.
.PP
When
\fBMERGE\fR
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
\fBINSERT \&.\&.\&. ON CONFLICT\fR
as an alternative statement which offers the ability to run an
\fBUPDATE\fR
if a concurrent
\fBINSERT\fR
occurs\&. There are a variety of differences and restrictions between the two statement types and they are not interchangeable\&.
.SH "EXAMPLES"
.PP
Perform maintenance on
customer_accounts
based upon new
recent_transactions\&.
.sp
.if n \{\
.RS 4
.\}
.nf
MERGE INTO customer_account ca
USING recent_transactions t
ON t\&.customer_id = ca\&.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t\&.customer_id, t\&.transaction_value);
.fi
.if n \{\
.RE
.\}
.PP
Notice that this would be exactly equivalent to the following statement because the
MATCHED
result does not change during execution\&.
.sp
.if n \{\
.RS 4
.\}
.nf
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
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t\&.customer_id, t\&.transaction_value);
.fi
.if n \{\
.RE
.\}
.PP
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\*(Aqt allow entries that have zero stock\&.
.sp
.if n \{\
.RS 4
.\}
.nf
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;
.fi
.if n \{\
.RE
.\}
.sp
The
wine_stock_changes
table might be, for example, a temporary table recently loaded into the database\&.
.SH "COMPATIBILITY"
.PP
This command conforms to the
SQL
standard\&.
.PP
The
WITH
clause and
DO NOTHING
action are extensions to the
SQL
standard\&.