sql-merge.html
text/html
Filename: sql-merge.html
Type: text/html
Part: 0
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>MERGE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets V1.79.1" /><link rel="prev" href="sql-lock.html" title="LOCK" /><link rel="next" href="sql-move.html" title="MOVE" /></head><body><div xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">MERGE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-lock.html" title="LOCK">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 11devel Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-move.html" title="MOVE">Next</a></td></tr></table><hr></hr></div><div class="refentry" id="SQL-MERGE"><div class="titlepage"></div><div class="refnamediv"><h2><span class="refentrytitle">MERGE</span></h2><p>MERGE — insert, update, or delete rows of a table based upon source data</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
MERGE INTO <em class="replaceable"><code>target_table_name</code></em> [ [ AS ] <em class="replaceable"><code>target_alias</code></em> ]
USING <em class="replaceable"><code>data_source</code></em>
ON <em class="replaceable"><code>join_condition</code></em>
<em class="replaceable"><code>when_clause</code></em> [...]
where <em class="replaceable"><code>data_source</code></em> is
{ <em class="replaceable"><code>source_table_name</code></em> |
( source_query )
}
[ [ AS ] <em class="replaceable"><code>source_alias</code></em> ]
and <em class="replaceable"><code>when_clause</code></em> is
{ WHEN MATCHED [ AND <em class="replaceable"><code>condition</code></em> ] THEN { <em class="replaceable"><code>merge_update</code></em> | <em class="replaceable"><code>merge_delete</code></em> } |
WHEN NOT MATCHED [ AND <em class="replaceable"><code>condition</code></em> ] THEN { <em class="replaceable"><code>merge_insert</code></em> | DO NOTHING }
}
and <em class="replaceable"><code>merge_update</code></em> is
UPDATE SET { <em class="replaceable"><code>column_name</code></em> = { <em class="replaceable"><code>expression</code></em> | DEFAULT } |
( <em class="replaceable"><code>column_name</code></em> [, ...] ) = ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] )
} [, ...]
and <em class="replaceable"><code>merge_insert</code></em> is
INSERT [( <em class="replaceable"><code>column_name</code></em> [, ...] )]
[ OVERRIDING { SYSTEM | USER } VALUE ]
{ VALUES ( { <em class="replaceable"><code>expression</code></em> | DEFAULT } [, ...] ) | DEFAULT VALUES }
and <em class="replaceable"><code>merge_delete</code></em> is
DELETE
</pre></div><div class="refsect1" id="id-1.9.3.156.4"><h2>Description</h2><p>
<code class="command">MERGE</code> performs actions that modify rows in the
<em class="replaceable"><code>target_table_name</code></em>,
using the <em class="replaceable"><code>data_source</code></em>.
<code class="command">MERGE</code> provides a single <acronym class="acronym">SQL</acronym>
statement that can conditionally <code class="command">INSERT</code>,
<code class="command">UPDATE</code> or <code class="command">DELETE</code> rows, a task
that would otherwise require multiple procedural language statements.
</p><p>
First, the <code class="command">MERGE</code> command performs a left outer join
from <em class="replaceable"><code>data_source</code></em> to
<em class="replaceable"><code>target_table_name</code></em>
producing zero or more candidate change rows. For each candidate change
row the status of <code class="literal">MATCHED</code> or <code class="literal">NOT MATCHED</code> is set
just once, after which <code class="literal">WHEN</code> clauses are evaluated
in the order specified. If one of them is activated, the specified
action occurs. No more than one <code class="literal">WHEN</code> clause can be
activated for any candidate change row.
</p><p>
<code class="command">MERGE</code> actions have the same effect as
regular <code class="command">UPDATE</code>, <code class="command">INSERT</code>, or
<code class="command">DELETE</code> commands of the same names. The syntax of
those commands is different, notably that there is no <code class="literal">WHERE</code>
clause and no tablename is specified. All actions refer to the
<em class="replaceable"><code>target_table_name</code></em>,
though modifications to other tables may be made using triggers.
</p><p>
There is no MERGE privilege.
You must have the <code class="literal">UPDATE</code> privilege on the column(s)
of the <em class="replaceable"><code>target_table_name</code></em>
referred to in the <code class="literal">SET</code> clause
if you specify an update action, the <code class="literal">INSERT</code> privilege
on the <em class="replaceable"><code>target_table_name</code></em>
if you specify an insert action and/or the <code class="literal">DELETE</code>
privilege on the <em class="replaceable"><code>target_table_name</code></em>
if you specify a delete action on the
<em class="replaceable"><code>target_table_name</code></em>.
Privileges are tested once at statement start and are checked
whether or not particular <code class="literal">WHEN</code> clauses are activated
during the subsequent execution.
You will require the <code class="literal">SELECT</code> privilege on the
<em class="replaceable"><code>data_source</code></em> and any column(s)
of the <em class="replaceable"><code>target_table_name</code></em>
referred to in a <code class="literal">condition</code>.
</p></div><div class="refsect1" id="id-1.9.3.156.5"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>target_table_name</code></em></span></dt><dd><p>
The name (optionally schema-qualified) of the target table to merge into.
</p></dd><dt><span class="term"><em class="replaceable"><code>target_alias</code></em></span></dt><dd><p>
A substitute name for the target table. When an alias is
provided, it completely hides the actual name of the table. For
example, given <code class="literal">MERGE foo AS f</code>, the remainder of the
<code class="command">MERGE</code> statement must refer to this table as
<code class="literal">f</code> not <code class="literal">foo</code>.
</p></dd><dt><span class="term"><em class="replaceable"><code>source_table_name</code></em></span></dt><dd><p>
The name (optionally schema-qualified) of the source table, view or
transition table.
</p></dd><dt><span class="term"><em class="replaceable"><code>source_query</code></em></span></dt><dd><p>
A query (<code class="command">SELECT</code> statement or <code class="command">VALUES</code>
statement) that supplies the rows to be merged into the
<em class="replaceable"><code>target_table_name</code></em>.
Refer to the <a class="xref" href="sql-select.html" title="SELECT"><span class="refentrytitle">SELECT</span></a>
statement or <a class="xref" href="sql-values.html" title="VALUES"><span class="refentrytitle">VALUES</span></a>
statement for a description of the syntax.
</p></dd><dt><span class="term"><em class="replaceable"><code>source_alias</code></em></span></dt><dd><p>
A substitute name for the data source. When an alias is
provided, it completely hides whether table or query was specified.
</p></dd><dt><span class="term"><em class="replaceable"><code>join_condition</code></em></span></dt><dd><p>
<em class="replaceable"><code>join_condition</code></em> is
an expression resulting in a value of type
<code class="type">boolean</code> (similar to a <code class="literal">WHERE</code>
clause) that specifies which rows in the
<em class="replaceable"><code>data_source</code></em>
match rows in the
<em class="replaceable"><code>target_table_name</code></em>.
</p></dd><dt><span class="term"><em class="replaceable"><code>when_clause</code></em></span></dt><dd><p>
At least one <code class="literal">WHEN</code> clause is required.
</p><p>
If the <code class="literal">WHEN</code> clause specifies <code class="literal">WHEN MATCHED</code>
and the candidate change row matches a row in the
<em class="replaceable"><code>target_table_name</code></em>
the <code class="literal">WHEN</code> clause is activated if the
<em class="replaceable"><code>condition</code></em> is
absent or is present and evaluates to <code class="literal">true</code>.
If the <code class="literal">WHEN</code> clause specifies <code class="literal">WHEN NOT MATCHED</code>
and the candidate change row does not match a row in the
<em class="replaceable"><code>target_table_name</code></em>
the <code class="literal">WHEN</code> clause is activated if the
<em class="replaceable"><code>condition</code></em> is
absent or is present and evaluates to <code class="literal">true</code>.
</p></dd><dt><span class="term"><em class="replaceable"><code>condition</code></em></span></dt><dd><p>
An expression that returns a value of type <code class="type">boolean</code>.
If this expression returns <code class="literal">true</code> then the <code class="literal">WHEN</code>
clause will be activated and the corresponding action will occur for
that row.
</p></dd><dt><span class="term"><em class="replaceable"><code>merge_insert</code></em></span></dt><dd><p>
The specification of an <code class="literal">INSERT</code> 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.
</p><p>
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.
</p><p>
If the expression for any column is not of the correct data type,
automatic type conversion will be attempted.
</p><p>
If <em class="replaceable"><code>target_table_name</code></em>
is a partitioned table, each row is routed to the appropriate partition
and inserted into it.
If <em class="replaceable"><code>target_table_name</code></em>
is a partition, an error will occur if one of the input rows violates
the partition constraint.
</p><p>
Do not include the table name, as you would normally do with an
<a class="xref" href="sql-insert.html" title="INSERT"><span class="refentrytitle">INSERT</span></a> command.
For example, <code class="literal">INSERT INTO tab VALUES (1, 50)</code> is invalid.
Column names may not be specified more than once.
<code class="command">INSERT</code> actions cannot contain sub-selects.
</p></dd><dt><span class="term"><em class="replaceable"><code>merge_update</code></em></span></dt><dd><p>
The specification of an <code class="literal">UPDATE</code> action that updates
the current row of the <em class="replaceable"><code>target_table_name</code></em>.
Column names may not be specified more than once.
</p><p>
Do not include the table name, as you would normally do with an
<a class="xref" href="sql-update.html" title="UPDATE"><span class="refentrytitle">UPDATE</span></a> command.
For example, <code class="literal">UPDATE tab SET col = 1</code> is invalid. Also,
do not include a <code class="literal">WHERE</code> clause, since only the current
row can be updated. For example,
<code class="literal">UPDATE SET col = 1 WHERE key = 57</code> is invalid.
<code class="command">UPDATE</code> actions cannot contain sub-selects in the
<code class="literal">SET</code> clause.
</p></dd><dt><span class="term"><em class="replaceable"><code>merge_delete</code></em></span></dt><dd><p>
Specifies a <code class="literal">DELETE</code> action that deletes the current row
of the <em class="replaceable"><code>target_table_name</code></em>.
Do not include the tablename or any other clauses, as you would normally
do with an <a class="xref" href="sql-delete.html" title="DELETE"><span class="refentrytitle">DELETE</span></a> command.
</p></dd><dt><span class="term"><em class="replaceable"><code>column_name</code></em></span></dt><dd><p>
The name of a column in the <em class="replaceable"><code>target_table_name</code></em>. 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.) When referencing a
column, do not include the table's name in the specification
of a target column.
</p></dd><dt><span class="term"><code class="literal">OVERRIDING SYSTEM VALUE</code></span></dt><dd><p>
Without this clause, it is an error to specify an explicit value
(other than <code class="literal">DEFAULT</code>) for an identity column defined
as <code class="literal">GENERATED ALWAYS</code>. This clause overrides that
restriction.
</p></dd><dt><span class="term"><code class="literal">OVERRIDING USER VALUE</code></span></dt><dd><p>
If this clause is specified, then any values supplied for identity
columns defined as <code class="literal">GENERATED BY DEFAULT</code> are ignored
and the default sequence-generated values are applied.
</p></dd><dt><span class="term"><code class="literal">DEFAULT VALUES</code></span></dt><dd><p>
All columns will be filled with their default values.
(An <code class="literal">OVERRIDING</code> clause is not permitted in this
form.)
</p></dd><dt><span class="term"><em class="replaceable"><code>expression</code></em></span></dt><dd><p>
An expression to assign to the column. The expression can use the
old values of this and other columns in the table.
</p></dd><dt><span class="term"><code class="literal">DEFAULT</code></span></dt><dd><p>
Set the column to its default value (which will be NULL if no
specific default expression has been assigned to it).
</p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.156.6"><h2>Outputs</h2><p>
On successful completion, a <code class="command">MERGE</code> command returns a command
tag of the form
</p><pre class="screen">
MERGE <em class="replaceable"><code>total-count</code></em>
</pre><p>
The <em class="replaceable"><code>total-count</code></em> is the total
number of rows changed (whether updated, inserted or deleted).
If <em class="replaceable"><code>total-count</code></em> is 0, no rows
were changed in any way.
</p></div><div class="refsect1" id="id-1.9.3.156.7"><h2>Notes</h2><p>
The following steps take place during the execution of
<code class="command">MERGE</code>.
</p><div class="orderedlist"><ol class="orderedlist" type="1"><li class="listitem"><p>
Perform any BEFORE STATEMENT triggers for all actions specified, whether or
not their <code class="literal">WHEN</code> clauses are activated during execution.
</p></li><li class="listitem"><p>
Perform left outer join from source to target table. Then for each
candidate change row
</p><div class="orderedlist"><ol class="orderedlist" type="a"><li class="listitem"><p>
Evaluate whether each row is MATCHED or NOT MATCHED.
</p></li><li class="listitem"><p>
Test each WHEN condition in the order specified until one activates.
</p></li><li class="listitem"><p>
When activated, perform the following actions
</p><div class="orderedlist"><ol class="orderedlist" type="i"><li class="listitem"><p>
Perform any BEFORE ROW triggers that fire for the action's event type.
</p></li><li class="listitem"><p>
Apply the action specified, invoking any check constraints on the
target table.
However, it will not invoke rules.
</p></li><li class="listitem"><p>
Perform any AFTER ROW triggers that fire for the action's event type.
</p></li></ol></div><p>
</p></li></ol></div><p>
</p></li><li class="listitem"><p>
Perform any AFTER STATEMENT triggers for actions specified, whether or
not they actually occur. This is similar to the behavior of an
<code class="command">UPDATE</code> statement that modifies no rows.
</p></li></ol></div><p>
In summary, statement triggers for an event type (say, INSERT) will
be fired whenever we <span class="emphasis"><em>specify</em></span> an action of that kind. Row-level
triggers will fire only for the one event type <span class="emphasis"><em>activated</em></span>.
So a <code class="command">MERGE</code> might fire statement triggers for both
<code class="command">UPDATE</code> and <code class="command">INSERT</code>, even though only
<code class="command">UPDATE</code> row triggers were fired.
</p><p>
The order in which rows are generated from the data source is indeterminate
by default. A <em class="replaceable"><code>source_query</code></em>
can be used to specify a consistent ordering, if required, which might be
needed to avoid deadlocks between concurrent transactions.
</p><p>
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 will
cause an error. This can also occur if row triggers make changes to the
target table which are then subsequently modified by <code class="command">MERGE</code>.
If the repeated action is an <code class="command">INSERT</code> this will
cause a uniqueness violation while a repeated <code class="command">UPDATE</code> or
<code class="command">DELETE</code> will cause a cardinality violation; the latter behavior
is required by the <acronym class="acronym">SQL</acronym> Standard. This differs from
historical <span class="productname">PostgreSQL</span> behavior of joins in
<code class="command">UPDATE</code> and <code class="command">DELETE</code> statements where second and
subsequent attempts to modify are simply ignored.
</p><p>
If the <code class="literal">ON</code> clause is a constant expression that evaluates to false
then no join takes place and the source is used directly as candidate change
rows.
</p><p>
If a <code class="literal">WHEN</code> clause omits an <code class="literal">AND</code> clause it becomes
the final reachable clause of that kind (<code class="literal">MATCHED</code> or
<code class="literal">NOT MATCHED</code>). If a later <code class="literal">WHEN</code> clause of that kind
is specified it would be provably unreachable and an error is raised.
If a final reachable clause is omitted it is possible that no action
will be taken for a candidate change row - it should be noted that no error
is raised if that occurs.
</p><p>
There is no <code class="literal">RETURNING</code> clause with <code class="command">MERGE</code>.
Actions of <code class="command">INSERT</code>, <code class="command">UPDATE</code> and <code class="command">DELETE</code>
cannot contain <code class="literal">RETURNING</code> or <code class="literal">WITH</code> clauses.
</p></div><div class="refsect1" id="id-1.9.3.156.8"><h2>Examples</h2><p>
Perform maintenance on CustomerAccounts based upon new Transactions.
</p><pre class="programlisting">
MERGE 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)
;
</pre><p>
notice that this would be exactly equivalent to the following
statement because the <code class="literal">MATCHED</code> result does not change
during execution
</p><pre class="programlisting">
MERGE 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
;
</pre><p>
</p><p>
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.
</p><pre class="programlisting">
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
;
</pre><p>
The wine_stock_changes table might be, for example, a temporary table
recently loaded into the database.
</p></div><div class="refsect1" id="id-1.9.3.156.9"><h2>Compatibility</h2><p>
This command conforms to the <acronym class="acronym">SQL</acronym> standard.
</p></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-lock.html">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-move.html">Next</a></td></tr><tr><td width="40%" align="left" valign="top">LOCK </td><td width="20%" align="center"><a accesskey="h" href="index.html">Home</a></td><td width="40%" align="right" valign="top"> MOVE</td></tr></table></div></body></html>