diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/merge.sgml | 603 |
1 files changed, 603 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml new file mode 100644 index 00000000000..a7d44a39b68 --- /dev/null +++ b/doc/src/sgml/ref/merge.sgml @@ -0,0 +1,603 @@ +<!-- +doc/src/sgml/ref/merge.sgml +PostgreSQL documentation +--> + +<refentry id="sql-merge"> + + <refmeta> + <refentrytitle>MERGE</refentrytitle> + <manvolnum>7</manvolnum> + <refmiscinfo>SQL - Language Statements</refmiscinfo> + </refmeta> + + <refnamediv> + <refname>MERGE</refname> + <refpurpose>insert, update, or delete rows of a table based upon source data</refpurpose> + </refnamediv> + + <refsynopsisdiv> +<synopsis> +MERGE INTO <replaceable class="parameter">target_table_name</replaceable> [ [ AS ] <replaceable class="parameter">target_alias</replaceable> ] +USING <replaceable class="parameter">data_source</replaceable> +ON <replaceable class="parameter">join_condition</replaceable> +<replaceable class="parameter">when_clause</replaceable> [...] + +where <replaceable class="parameter">data_source</replaceable> is + +{ <replaceable class="parameter">source_table_name</replaceable> | + ( source_query ) +} +[ [ AS ] <replaceable class="parameter">source_alias</replaceable> ] + +and <replaceable class="parameter">when_clause</replaceable> is + +{ WHEN MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_update</replaceable> | <replaceable class="parameter">merge_delete</replaceable> } | + WHEN NOT MATCHED [ AND <replaceable class="parameter">condition</replaceable> ] THEN { <replaceable class="parameter">merge_insert</replaceable> | DO NOTHING } +} + +and <replaceable class="parameter">merge_insert</replaceable> is + +INSERT [( <replaceable class="parameter">column_name</replaceable> [, ...] )] +[ OVERRIDING { SYSTEM | USER } VALUE ] +{ VALUES ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) | DEFAULT VALUES } + +and <replaceable class="parameter">merge_update</replaceable> is + +UPDATE SET { <replaceable class="parameter">column_name</replaceable> = { <replaceable class="parameter">expression</replaceable> | DEFAULT } | + ( <replaceable class="parameter">column_name</replaceable> [, ...] ) = ( { <replaceable class="parameter">expression</replaceable> | DEFAULT } [, ...] ) + } [, ...] + +and <replaceable class="parameter">merge_delete</replaceable> is + +DELETE +</synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Description</title> + + <para> + <command>MERGE</command> performs actions that modify rows in the + <replaceable class="parameter">target_table_name</replaceable>, + using the <replaceable class="parameter">data_source</replaceable>. + <command>MERGE</command> provides a single <acronym>SQL</acronym> + statement that can conditionally <command>INSERT</command>, + <command>UPDATE</command> or <command>DELETE</command> rows, a task + that would otherwise require multiple procedural language statements. + </para> + + <para> + First, the <command>MERGE</command> command performs a join + from <replaceable class="parameter">data_source</replaceable> to + <replaceable class="parameter">target_table_name</replaceable> + producing zero or more candidate change rows. For each candidate change + row the status of <literal>MATCHED</literal> or <literal>NOT MATCHED</literal> is set + just once, after which <literal>WHEN</literal> clauses are evaluated + in the order specified. If one of them is activated, the specified + action occurs. No more than one <literal>WHEN</literal> clause can be + activated for any candidate change 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. The syntax of + those commands is different, notably that there is no <literal>WHERE</literal> + clause and no tablename is specified. All actions refer to the + <replaceable class="parameter">target_table_name</replaceable>, + though modifications to other tables may be made using triggers. + </para> + + <para> + When <literal>DO NOTHING</literal> action is specified, the source row is + skipped. Since actions are evaluated in the given order, <literal>DO + NOTHING</literal> can be handy to skip non-interesting source rows before + more fine-grained handling. + </para> + + <para> + There is no MERGE privilege. + You must have the <literal>UPDATE</literal> privilege on the column(s) + of the <replaceable class="parameter">target_table_name</replaceable> + referred to in the <literal>SET</literal> clause + if you specify an update action, the <literal>INSERT</literal> privilege + on the <replaceable class="parameter">target_table_name</replaceable> + if you specify an insert action and/or the <literal>DELETE</literal> + privilege on the <replaceable class="parameter">target_table_name</replaceable> + if you specify a delete action on the + <replaceable class="parameter">target_table_name</replaceable>. + Privileges are tested once at statement start and are checked + whether or not particular <literal>WHEN</literal> clauses are activated + during the subsequent execution. + You will require the <literal>SELECT</literal> privilege on the + <replaceable class="parameter">data_source</replaceable> and any column(s) + of the <replaceable class="parameter">target_table_name</replaceable> + referred to in a <literal>condition</literal>. + </para> + + <para> + MERGE is not supported if the <replaceable + class="parameter">target_table_name</replaceable> has + <literal>RULES</literal> defined on it. + See <xref linkend="rules"/> for more information about <literal>RULES</literal>. + </para> + </refsect1> + + <refsect1> + <title>Parameters</title> + + <variablelist> + <varlistentry> + <term><replaceable class="parameter">target_table_name</replaceable></term> + <listitem> + <para> + The name (optionally schema-qualified) of the target table to merge into. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">target_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</literal>, the remainder of the + <command>MERGE</command> statement must refer to this table as + <literal>f</literal> not <literal>foo</literal>. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">source_table_name</replaceable></term> + <listitem> + <para> + The name (optionally schema-qualified) of the source table, view or + transition table. + </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 + <replaceable class="parameter">target_table_name</replaceable>. + Refer to the <xref linkend="sql-select"/> + statement or <xref linkend="sql-values"/> + statement for a description of the syntax. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">source_alias</replaceable></term> + <listitem> + <para> + A substitute name for the data source. When an alias is + provided, it completely hides whether table or query was specified. + </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 + <replaceable class="parameter">data_source</replaceable> + match rows in the + <replaceable class="parameter">target_table_name</replaceable>. + </para> + <warning> + <para> + Only columns from <replaceable class="parameter">target_table_name</replaceable> + that attempt to match <replaceable class="parameter">data_source</replaceable> + rows should appear in <replaceable class="parameter">join_condition</replaceable>. + <replaceable class="parameter">join_condition</replaceable> subexpressions that + only reference <replaceable class="parameter">target_table_name</replaceable> + columns can only affect which action is taken, often in surprising ways. + </para> + </warning> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">when_clause</replaceable></term> + <listitem> + <para> + At least one <literal>WHEN</literal> clause is required. + </para> + <para> + If the <literal>WHEN</literal> clause specifies <literal>WHEN MATCHED</literal> + and the candidate change row matches a row in the + <replaceable class="parameter">target_table_name</replaceable> + the <literal>WHEN</literal> clause is activated if the + <replaceable class="parameter">condition</replaceable> is + absent or is present and evaluates to <literal>true</literal>. + If the <literal>WHEN</literal> clause specifies <literal>WHEN NOT MATCHED</literal> + and the candidate change row does not match a row in the + <replaceable class="parameter">target_table_name</replaceable> + the <literal>WHEN</literal> clause is activated if the + <replaceable class="parameter">condition</replaceable> is + absent or is present and evaluates to <literal>true</literal>. + </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</literal> then the <literal>WHEN</literal> + clause will be activated and the corresponding action will occur for + that row. The expression may not contain functions that possibly performs + writes to the database. + </para> + <para> + A condition on a <literal>WHEN MATCHED</literal> clause can refer to columns + in both the source and the target relation. A condition on a + <literal>WHEN NOT MATCHED</literal> 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. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">merge_insert</replaceable></term> + <listitem> + <para> + The specification of an <literal>INSERT</literal> 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. + </para> + <para> + 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. + </para> + <para> + If the expression for any column is not of the correct data type, + automatic type conversion will be attempted. + </para> + <para> + If <replaceable class="parameter">target_table_name</replaceable> + is a partitioned table, each row is routed to the appropriate partition + and inserted into it. + If <replaceable class="parameter">target_table_name</replaceable> + is a partition, an error will occur if one of the input rows violates + the partition constraint. + </para> + <para> + Column names may not be specified more than once. + <command>INSERT</command> actions cannot contain sub-selects. + </para> + <para> + The <literal>VALUES</literal> clause can only refer to columns from + the source relation, since by definition there is no matching target row. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">merge_update</replaceable></term> + <listitem> + <para> + The specification of an <literal>UPDATE</literal> action that updates + the current row of the <replaceable + class="parameter">target_table_name</replaceable>. + Column names may not be specified more than once. + </para> + <para> + Do not include the table name, as you would normally do with an + <xref linkend="sql-update"/> command. + For example, <literal>UPDATE tab SET col = 1</literal> is invalid. Also, + do not include a <literal>WHERE</literal> clause, since only the current + row can be updated. For example, + <literal>UPDATE SET col = 1 WHERE key = 57</literal> is invalid. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">merge_delete</replaceable></term> + <listitem> + <para> + Specifies a <literal>DELETE</literal> action that deletes the current row + of the <replaceable class="parameter">target_table_name</replaceable>. + Do not include the tablename or any other clauses, as you would normally + do with an <xref linkend="sql-delete"/> command. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="parameter">column_name</replaceable></term> + <listitem> + <para> + The name of a column in the <replaceable + class="parameter">target_table_name</replaceable>. 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. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>OVERRIDING SYSTEM VALUE</literal></term> + <listitem> + <para> + Without this clause, it is an error to specify an explicit value + (other than <literal>DEFAULT</literal>) for an identity column defined + as <literal>GENERATED ALWAYS</literal>. This clause overrides that + restriction. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>OVERRIDING USER VALUE</literal></term> + <listitem> + <para> + If this clause is specified, then any values supplied for identity + columns defined as <literal>GENERATED BY DEFAULT</literal> are ignored + and the default sequence-generated values are applied. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>DEFAULT VALUES</literal></term> + <listitem> + <para> + All columns will be filled with their default values. + (An <literal>OVERRIDING</literal> clause is not permitted in this + form.) + </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> 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 total + number of rows changed (whether inserted, updated, or deleted). + If <replaceable class="parameter">total-count</replaceable> is 0, no rows + were changed in any way. + </para> + + </refsect1> + + <refsect1> + <title>Execution</title> + + <para> + The following steps take place during the execution of + <command>MERGE</command>. + <orderedlist> + <listitem> + <para> + Perform any BEFORE STATEMENT triggers for all actions specified, whether or + not their <literal>WHEN</literal> clauses are activated during execution. + </para> + </listitem> + <listitem> + <para> + Perform a join from source to target table. + The resulting query will be optimized normally and will produce + a set of candidate change row. For each candidate change 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. + </para> + </listitem> + <listitem> + <para> + When activated, perform the following actions + <orderedlist> + <listitem> + <para> + Perform any BEFORE ROW triggers that fire for the action's event type. + </para> + </listitem> + <listitem> + <para> + Apply the action specified, invoking any check constraints on the + target table. + However, it will not invoke rules. + </para> + </listitem> + <listitem> + <para> + Perform any AFTER ROW triggers that fire for the action's event type. + </para> + </listitem> + </orderedlist> + </para> + </listitem> + </orderedlist> + </para> + </listitem> + <listitem> + <para> + Perform any AFTER STATEMENT triggers for actions specified, whether or + not they actually occur. This is similar to the behavior of an + <command>UPDATE</command> statement that modifies no rows. + </para> + </listitem> + </orderedlist> + In summary, statement triggers for an event type (say, INSERT) will + be fired whenever we <emphasis>specify</emphasis> an action of that kind. Row-level + triggers will fire only for the one event type <emphasis>activated</emphasis>. + So a <command>MERGE</command> might fire statement triggers for both + <command>UPDATE</command> and <command>INSERT</command>, even though only + <command>UPDATE</command> row triggers were fired. + </para> + + <para> + 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 <command>MERGE</command>. + If the repeated action is an <command>INSERT</command> this will + cause a uniqueness violation while a repeated <command>UPDATE</command> or + <command>DELETE</command> will cause a cardinality violation; the latter behavior + is required by the <acronym>SQL</acronym> Standard. This differs from + historical <productname>PostgreSQL</productname> behavior of joins in + <command>UPDATE</command> and <command>DELETE</command> statements where second and + subsequent attempts to modify are simply ignored. + </para> + + <para> + If a <literal>WHEN</literal> clause omits an <literal>AND</literal> clause it becomes + the final reachable clause of that kind (<literal>MATCHED</literal> or + <literal>NOT MATCHED</literal>). If a later <literal>WHEN</literal> 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. + </para> + + </refsect1> + <refsect1> + <title>Notes</title> + + <para> + The order in which rows are generated from the data source is indeterminate + by default. A <replaceable class="parameter">source_query</replaceable> + can be used to specify a consistent ordering, if required, which might be + needed to avoid deadlocks between concurrent transactions. + </para> + + <para> + There is no <literal>RETURNING</literal> clause with <command>MERGE</command>. + Actions of <command>INSERT</command>, <command>UPDATE</command> and <command>DELETE</command> + cannot contain <literal>RETURNING</literal> or <literal>WITH</literal> clauses. + </para> + + <tip> + <para> + You may also wish to consider using <command>INSERT ... ON CONFLICT</command> as an + alternative statement which offers the ability to run an <command>UPDATE</command> + if a concurrent <command>INSERT</command> occurs. There are a variety of + differences and restrictions between the two statement types and they are not + interchangeable. + </para> + </tip> + </refsect1> + + <refsect1> + <title>Examples</title> + + <para> + Perform maintenance on CustomerAccounts based upon new Transactions. + +<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); +</programlisting> + + notice that this would be exactly equivalent to the following + statement because the <literal>MATCHED</literal> result does not change + during execution + +<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; +</programlisting> + </para> + + <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. Don't allow entries that have zero stock. +<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; +</programlisting> + + The wine_stock_changes table might be, for example, a temporary table + recently loaded into the database. + </para> + + </refsect1> + + <refsect1> + <title>Compatibility</title> + <para> + This command conforms to the <acronym>SQL</acronym> standard. + </para> + <para> + The DO NOTHING action is an extension to the <acronym>SQL</acronym> standard. + </para> + </refsect1> +</refentry> |