On Mon, 2008-04-21 at 21:08 +0100, Simon Riggs wrote:
> The following two files specify the behaviour of the MERGE statement and
> how it will work in the world of PostgreSQL.
> The HTML file was generated from SGML source, though the latter is not
> included here for clarity.
Enclose merge.sgml docs for forthcoming MERGE command, as originally
written.
--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Training and Services
<!--
$PostgreSQL$
-->
<refentry id="SQL-MERGE">
<refmeta>
<refentrytitle id="SQL-MERGE-TITLE">MERGE</refentrytitle>
<refmiscinfo>SQL - Language Statements</refmiscinfo>
</refmeta>
<refnamediv>
<refname>MERGE</refname>
<refpurpose>update, insert or delete rows of a table based upon source data</refpurpose>
</refnamediv>
<indexterm zone="sql-merge">
<primary>MERGE</primary>
</indexterm>
<refsynopsisdiv>
<synopsis>
MERGE INTO <replaceable class="PARAMETER">table</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> ]
USING <replaceable class="PARAMETER">source-query</replaceable>
ON <replaceable class="PARAMETER">join_condition</replaceable>
[<replaceable class="PARAMETER">when_clause</replaceable> [...]]
where <replaceable class="PARAMETER">when_clause</replaceable> is
{ WHEN MATCHED [ AND <replaceable class="PARAMETER">condition</replaceable> ] THEN { <replaceable class="PARAMETER">merge_update</replaceable> | DELETE }
WHEN NOT MATCHED [ AND <replaceable class="PARAMETER">condition</replaceable> ] THEN { <replaceable class="PARAMETER">merge_insert</replaceable> | DO NOTHING } }
where <replaceable class="PARAMETER">merge_update</replaceable> is
UPDATE SET { <replaceable class="PARAMETER">column</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } |
( <replaceable class="PARAMETER">column</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) } [, ...]
and <replaceable class="PARAMETER">merge_insert</replaceable> is
INSERT [( <replaceable class="PARAMETER">column</replaceable> [, ...] )] { VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES }
</synopsis>
</refsynopsisdiv>
<refsect1>
<title>Description</title>
<para>
<command>MERGE</command> performs at most one action on each row from
the target table, driven by the rows from the source query. This
provides a way to specify a single SQL statement that can conditionally
<command>UPDATE</command> or <command>INSERT</command> rows, a task
that would otherwise require multiple procedural language statements.
</para>
<para>
First, the <command>MERGE</command> command performs a left outer join
from source query to target table, producing zero or more merged rows. For
each merged row, <literal>WHEN</> clauses are evaluated in the
specified order until one of them is activated. The corresponding action
is then applied and processing continues for the next row.
</para>
<para>
<command>MERGE</command> actions have the same effect as
regular <command>UPDATE</command>, <command>INSERT</command>, or
<command>DELETE</command> commands of the same names, though the syntax
is slightly different.
</para>
<para>
If no <literal>WHEN</> clause activates then an implicit action of
<literal>INSERT DEFAULT VALUES</> is performed for that row. If that
implicit action is not desirable an explicit action of
<literal>DO NOTHING</> may be specified instead.
</para>
<para>
<command>MERGE</command> will only affect rows only in the specified table.
</para>
<para>
There is no <literal>RETURNING</> clause with <command>MERGE</command>.
</para>
<para>
There is no MERGE privilege.
You must have the <literal>UPDATE</literal> privilege on the table
if you specify an update action, the <literal>INSERT</literal> privilege if
you specify an insert action and/or the <literal>DELETE</literal> privilege
if you wish to delete. You will also require the
<literal>SELECT</literal> privilege to any table whose values are read
in the <replaceable class="parameter">expressions</replaceable> or
<replaceable class="parameter">condition</replaceable>.
</para>
</refsect1>
<refsect1>
<title>Parameters</title>
<variablelist>
<varlistentry>
<term><replaceable class="PARAMETER">table</replaceable></term>
<listitem>
<para>
The name (optionally schema-qualified) of the table to merge into.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="parameter">alias</replaceable></term>
<listitem>
<para>
A substitute name for the target table. When an alias is
provided, it completely hides the actual name of the table. For
example, given <literal>MERGE foo AS f</>, the remainder of the
<command>MERGE</command> statement must refer to this table as
<literal>f</> not <literal>foo</>.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">source-query</replaceable></term>
<listitem>
<para>
A query (<command>SELECT</command> statement or <command>VALUES</command>
statement) that supplies the rows to be merged into the target table.
Refer to the <xref linkend="sql-select" endterm="sql-select-title">
statement or <xref linkend="sql-values" endterm="sql-values-title">
statement for a description of the syntax.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">join_condition</replaceable></term>
<listitem>
<para>
<replaceable class="parameter">join_condition</replaceable> is
an expression resulting in a value of type
<type>boolean</type> (similar to a <literal>WHERE</literal>
clause) that specifies which rows in the join are considered to
match. You should ensure that the join produces at most one output
row for each row to be modified. An attempt to modify any row of the
target table more than once will result in an error. This behaviour
requires the user to take greater care in using <command>MERGE</command>,
though is required explicitly by the SQL Standard.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">condition</replaceable></term>
<listitem>
<para>
An expression that returns a value of type <type>boolean</type>.
If this expression returns <literal>true</> then the <literal>WHEN</>
clause will be activated and the corresponding action will occur for
that row.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">merge_update</replaceable></term>
<listitem>
<para>
The specification of an <literal>UPDATE</> action. Do not include
the table name, as you would normally do with an
<xref linkend="sql-update" endterm="sql-update-title"> command.
For example, <literal>UPDATE tab SET col = 1</> is invalid. Also,
do not include a <literal>WHERE</> clause, since only the current
can be updated. For example,
<literal>UPDATE SET col = 1 WHERE key = 57</> is invalid.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">merge_insert</replaceable></term>
<listitem>
<para>
The specification of an <literal>INSERT</> action. Do not include
the table name, as you would normally do with an
<xref linkend="sql-insert" endterm="sql-insert-title"> command.
For example, <literal>INSERT INTO tab VALUES (1, 50)</> is invalid.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">column</replaceable></term>
<listitem>
<para>
The name of a column in <replaceable
class="PARAMETER">table</replaceable>.
The column name can be qualified with a subfield name or array
subscript, if needed. Do not include the table's name in the
specification of a target column — for example,
<literal>UPDATE SET tab.col = 1</> is invalid.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><replaceable class="PARAMETER">expression</replaceable></term>
<listitem>
<para>
An expression to assign to the column. The expression can use the
old values of this and other columns in the table.
</para>
</listitem>
</varlistentry>
<varlistentry>
<term><literal>DEFAULT</literal></term>
<listitem>
<para>
Set the column to its default value (which will be NULL if no
specific default expression has been assigned to it).
</para>
</listitem>
</varlistentry>
</variablelist>
</refsect1>
<refsect1>
<title>Outputs</title>
<para>
On successful completion, a <command>MERGE</> command returns a command
tag of the form
<screen>
MERGE <replaceable class="parameter">total-count</replaceable>
</screen>
The <replaceable class="parameter">total-count</replaceable> is the number
of rows changed (either updated, inserted or deleted).
If <replaceable class="parameter">total-count</replaceable> is 0, no rows
were changed (this is not considered an error).
</para>
<para>
The number of rows updated, inserted or deleted is not available as part
of the command tag. An optional NOTIFY message can be generated to
present this information, if desired.
<screen>
NOTIFY: 34 rows processed: 11 updated, 5 deleted, 15 inserted, 3 default inserts, 0 no action
</screen>
</para>
</refsect1>
<refsect1>
<title>Notes</title>
<para>
What essentially happens is that the target table is left outer-joined to
the tables mentioned in the <replaceable>source-query</replaceable>, and
each output row of the join may then activate at most one when-clause.
The row will be matched only once per statement, so the status of
<literal>MATCHED</> or <literal>NOT MATCHED</> cannot change once testing
of <literal>WHEN</> clauses has begun. <command>MERGE</command> will not
invoke Rules.
</para>
<para>
The following steps take place during the execution of
<command>MERGE</command>.
<orderedlist>
<listitem>
<para>
Perform any BEFORE STATEMENT triggers for actions specified, whether or
not they actually occur.
</para>
</listitem>
<listitem>
<para>
Perform left outer join from source to target table. Then for each row:
<orderedlist>
<listitem>
<para>
Evaluate whether each row is MATCHED or NOT MATCHED.
</para>
</listitem>
<listitem>
<para>
Test each WHEN condition in the order specified until one activates.
Identify the action and its event type.
</para>
</listitem>
<listitem>
<para>
Perform any BEFORE ROW triggers that fire for the action's event type.
</para>
</listitem>
<listitem>
<para>
Apply the action specified.
</para>
</listitem>
<listitem>
<para>
Perform any AFTER ROW triggers that fire for the action's event type.
</para>
</listitem>
</orderedlist>
</para>
</listitem>
<listitem>
<para>
Perform any AFTER STATEMENT triggers for actions specified, whether or
not they actually occur.
</para>
</listitem>
</orderedlist>
In summary, statement triggers for an event type (say, INSERT) will
be fired whenever we <emphasis>specify</> an action of that kind. Row-level
triggers will fire only for event type <emphasis>activated</>.
So a <command>MERGE</command> might fire statement triggers for both
<literal>UPDATE</> and <literal>INSERT</>, even though only
<literal>UPDATE</> row triggers were fired.
</para>
</refsect1>
<refsect1>
<title>Examples</title>
<para>
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.
<programlisting>
MERGE INTO wines w
USING (VALUES('Chateau Lafite 2003', '24')) v
ON v.column1 = w.winename
WHEN NOT MATCHED
INSERT VALUES(v.column1, v.column2)
WHEN MATCHED
UPDATE SET stock = stock + v.column2;
</programlisting>
</para>
<para>
Perform maintenance on CustomerAccounts based upon new Transactions.
The following statement will fail if any accounts have had more than
one transaction
<programlisting>
MERGE CustomerAccount CA
USING (SELECT CustomerId, TransactionValue,
FROM Transactions
WHERE TransactionId > 35345678) AS T
ON T.CustomerId = CA.CustomerId
WHEN MATCHED
UPDATE SET Balance = Balance - TransactionValue
WHEN NOT MATCHED
INSERT (CustomerId, Balance)
VALUES (T.CustomerId, T.TransactionValue)
;
</programlisting>
so the right way to do this is to pre-aggregate the data
<programlisting>
MERGE CustomerAccount CA
USING (SELECT CustomerId, Sum(TransactionValue) As TransactionSum
FROM Transactions
WHERE TransactionId > 35345678
GROUP BY CustomerId) AS T
ON T.CustomerId = CA.CustomerId
WHEN MATCHED
UPDATE SET Balance = Balance - TransactionSum
WHEN NOT MATCHED
INSERT (CustomerId, Balance)
VALUES (T.CustomerId, T.TransactionSum)
;
</programlisting>
</para>
</refsect1>
<refsect1>
<title>Compatibility</title>
<para>
This command conforms to the <acronym>SQL</acronym> standard, except
that the <literal>DELETE</literal> and <literal>DO NOTHING</> actions
are <productname>PostgreSQL</productname> extensions.
</para>
<para>
According to the standard, the column-list syntax for an <literal>UPDATE</>
action should allow a list of columns to be assigned from a single
row-valued expression.
This is not currently implemented — the source must be a list
of independent expressions.
</para>
</refsect1>
</refentry>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
