From aa5877bb26347c58a34aee4e460eb1e1123bb096 Mon Sep 17 00:00:00 2001 From: Simon Riggs Date: Mon, 2 Apr 2018 21:36:38 +0100 Subject: Revert "MERGE SQL Command following SQL:2016" This reverts commit e6597dc3533946b98acba7871bd4ca1f7a3d4c1d. --- doc/src/sgml/ref/merge.sgml | 603 -------------------------------------------- 1 file changed, 603 deletions(-) delete mode 100644 doc/src/sgml/ref/merge.sgml (limited to 'doc/src') diff --git a/doc/src/sgml/ref/merge.sgml b/doc/src/sgml/ref/merge.sgml deleted file mode 100644 index a7d44a39b6..0000000000 --- a/doc/src/sgml/ref/merge.sgml +++ /dev/null @@ -1,603 +0,0 @@ - - - - - - MERGE - 7 - SQL - Language Statements - - - - MERGE - insert, update, or delete rows of a table based upon source data - - - - -MERGE INTO target_table_name [ [ AS ] target_alias ] -USING data_source -ON join_condition -when_clause [...] - -where data_source is - -{ source_table_name | - ( source_query ) -} -[ [ AS ] source_alias ] - -and when_clause is - -{ WHEN MATCHED [ AND condition ] THEN { merge_update | merge_delete } | - WHEN NOT MATCHED [ AND condition ] THEN { merge_insert | DO NOTHING } -} - -and merge_insert is - -INSERT [( column_name [, ...] )] -[ OVERRIDING { SYSTEM | USER } VALUE ] -{ VALUES ( { expression | DEFAULT } [, ...] ) | DEFAULT VALUES } - -and merge_update is - -UPDATE SET { column_name = { expression | DEFAULT } | - ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) - } [, ...] - -and merge_delete is - -DELETE - - - - - Description - - - MERGE performs actions that modify rows in the - target_table_name, - using the data_source. - MERGE provides a single SQL - statement that can conditionally INSERT, - UPDATE or DELETE rows, a task - that would otherwise require multiple procedural language statements. - - - - First, the MERGE command performs a join - from data_source to - target_table_name - 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. If one of them is activated, the specified - action occurs. No more than one WHEN clause can be - activated for any candidate change row. - - - - MERGE actions have the same effect as - regular UPDATE, INSERT, or - DELETE commands of the same names. The syntax of - those commands is different, notably that there is no WHERE - clause and no tablename is specified. All actions refer to the - target_table_name, - though modifications to other tables may be made using triggers. - - - - When DO NOTHING action is specified, the source row is - skipped. Since actions are evaluated in the given order, DO - NOTHING can be handy to skip non-interesting source rows before - more fine-grained handling. - - - - There is no MERGE privilege. - You must have the UPDATE privilege on the column(s) - of the target_table_name - referred to in the SET clause - if you specify an update action, the INSERT privilege - on the target_table_name - if you specify an insert action and/or the DELETE - privilege on the target_table_name - if you specify a delete action on the - target_table_name. - Privileges are tested once at statement start and are checked - whether or not particular WHEN clauses are activated - during the subsequent execution. - You will require the SELECT privilege on the - data_source and any column(s) - of the target_table_name - referred to in a condition. - - - - MERGE is not supported if the target_table_name has - RULES defined on it. - See for more information about RULES. - - - - - Parameters - - - - target_table_name - - - The name (optionally schema-qualified) of the target table to merge into. - - - - - - target_alias - - - 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 foo AS f, the remainder of the - MERGE statement must refer to this table as - f not foo. - - - - - - source_table_name - - - The name (optionally schema-qualified) of the source table, view or - transition table. - - - - - - source_query - - - A query (SELECT statement or VALUES - statement) that supplies the rows to be merged into the - target_table_name. - Refer to the - statement or - statement for a description of the syntax. - - - - - - source_alias - - - A substitute name for the data source. When an alias is - provided, it completely hides whether table or query was specified. - - - - - - join_condition - - - join_condition is - an expression resulting in a value of type - boolean (similar to a WHERE - clause) that specifies which rows in the - data_source - match rows in the - target_table_name. - - - - Only columns from target_table_name - that attempt to match data_source - rows should appear in join_condition. - join_condition subexpressions that - only reference target_table_name - columns can only affect which action is taken, often in surprising ways. - - - - - - - when_clause - - - At least one WHEN clause is required. - - - If the WHEN clause specifies WHEN MATCHED - and the candidate change row matches a row in the - target_table_name - the WHEN clause is activated if the - condition is - absent or is present and evaluates to true. - If the WHEN clause specifies WHEN NOT MATCHED - and the candidate change row does not match a row in the - target_table_name - the WHEN clause is activated if the - condition is - absent or is present and evaluates to true. - - - - - - condition - - - An expression that returns a value of type boolean. - If this expression returns true then the WHEN - 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. - - - A condition on a WHEN MATCHED clause can refer to columns - in both the source and the target relation. 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. - - - - - - merge_insert - - - 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. - - - 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. - - - If the expression for any column is not of the correct data type, - automatic type conversion will be attempted. - - - If target_table_name - is a partitioned table, each row is routed to the appropriate partition - and inserted into it. - If target_table_name - is a partition, an error will occur if one of the input rows violates - the partition constraint. - - - Column names may not be specified more than once. - INSERT actions cannot contain sub-selects. - - - The VALUES clause can only refer to columns from - the source relation, since by definition there is no matching target row. - - - - - - merge_update - - - The specification of an UPDATE action that updates - the current row of the target_table_name. - Column names may not be specified more than once. - - - Do not include the table name, as you would normally do with an - command. - For example, UPDATE tab SET col = 1 is invalid. Also, - do not include a WHERE clause, since only the current - row can be updated. For example, - UPDATE SET col = 1 WHERE key = 57 is invalid. - - - - - - merge_delete - - - Specifies a DELETE action that deletes the current row - of the target_table_name. - Do not include the tablename or any other clauses, as you would normally - do with an command. - - - - - - column_name - - - The name of a column in the target_table_name. 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. - - - - - - OVERRIDING SYSTEM VALUE - - - 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. - - - - - - OVERRIDING USER VALUE - - - 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. - - - - - - DEFAULT VALUES - - - All columns will be filled with their default values. - (An OVERRIDING clause is not permitted in this - form.) - - - - - - expression - - - An expression to assign to the column. The expression can use the - old values of this and other columns in the table. - - - - - - DEFAULT - - - Set the column to its default value (which will be NULL if no - specific default expression has been assigned to it). - - - - - - - - - Outputs - - - On successful completion, a MERGE command returns a command - tag of the form - -MERGE total-count - - The total-count is the total - number of rows changed (whether inserted, updated, or deleted). - If total-count is 0, no rows - were changed in any way. - - - - - - Execution - - - The following steps take place during the execution of - MERGE. - - - - Perform any BEFORE STATEMENT triggers for all actions specified, whether or - not their WHEN clauses are activated during execution. - - - - - 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 - - - - Evaluate whether each row is MATCHED or NOT MATCHED. - - - - - Test each WHEN condition in the order specified until one activates. - - - - - When activated, perform the following actions - - - - Perform any BEFORE ROW triggers that fire for the action's event type. - - - - - Apply the action specified, invoking any check constraints on the - target table. - However, it will not invoke rules. - - - - - Perform any AFTER ROW triggers that fire for the action's event type. - - - - - - - - - - - Perform any AFTER STATEMENT triggers for actions specified, whether or - not they actually occur. This is similar to the behavior of an - UPDATE statement that modifies no rows. - - - - In summary, statement triggers for an event type (say, INSERT) will - be fired whenever we specify an action of that kind. Row-level - triggers will fire only for the one event type activated. - So a MERGE might fire statement triggers for both - UPDATE and INSERT, even though only - UPDATE row triggers were fired. - - - - 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 MERGE. - If the repeated action is an INSERT this will - cause a uniqueness violation while a repeated UPDATE or - DELETE will cause a cardinality violation; the latter behavior - is required by the SQL Standard. This differs from - historical PostgreSQL behavior of joins in - UPDATE and DELETE statements where second and - subsequent attempts to modify are simply ignored. - - - - If a WHEN clause omits an AND 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 a final reachable clause is omitted it is possible that no action - will be taken for a candidate change row. - - - - - Notes - - - The order in which rows are generated from the data source is indeterminate - by default. A source_query - can be used to specify a consistent ordering, if required, which might be - needed to avoid deadlocks between concurrent transactions. - - - - There is no RETURNING clause with MERGE. - Actions of INSERT, UPDATE and DELETE - cannot contain RETURNING or WITH clauses. - - - - - You may also wish to consider using INSERT ... ON CONFLICT as an - alternative statement which offers the ability to run an UPDATE - if a concurrent INSERT occurs. There are a variety of - differences and restrictions between the two statement types and they are not - interchangeable. - - - - - - Examples - - - Perform maintenance on CustomerAccounts based upon new Transactions. - - -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); - - - notice that this would be exactly equivalent to the following - statement because the MATCHED result does not change - during execution - - -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; - - - - - 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. - -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; - - - The wine_stock_changes table might be, for example, a temporary table - recently loaded into the database. - - - - - - Compatibility - - This command conforms to the SQL standard. - - - The DO NOTHING action is an extension to the SQL standard. - - - -- cgit v1.2.1