diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/ref/merge.sgml | 603 |
1 files changed, 0 insertions, 603 deletions
diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml deleted file mode 100644 index a7d44a39b68..00000000000 --- a/doc/src/sgml/ref/merge.sgml +++ /dev/null @@ -1,603 +0,0 @@ -<!-- -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> |