From e6597dc3533946b98acba7871bd4ca1f7a3d4c1d Mon Sep 17 00:00:00 2001 From: Simon Riggs Date: Mon, 2 Apr 2018 21:04:35 +0100 Subject: MERGE SQL Command following SQL:2016 MERGE performs actions that modify rows in the target table using a source table or query. MERGE provides a single SQL statement that can conditionally INSERT/UPDATE/DELETE rows a task that would other require multiple PL statements. e.g. MERGE INTO target AS t USING source AS s ON t.tid = s.sid WHEN MATCHED AND t.balance > s.delta THEN UPDATE SET balance = t.balance - s.delta WHEN MATCHED THEN DELETE WHEN NOT MATCHED AND s.delta > 0 THEN INSERT VALUES (s.sid, s.delta) WHEN NOT MATCHED THEN DO NOTHING; MERGE works with regular and partitioned tables, including column and row security enforcement, as well as support for row, statement and transition triggers. MERGE is optimized for OLTP and is parameterizable, though also useful for large scale ETL/ELT. MERGE is not intended to be used in preference to existing single SQL commands for INSERT, UPDATE or DELETE since there is some overhead. MERGE can be used statically from PL/pgSQL. MERGE does not yet support inheritance, write rules, RETURNING clauses, updatable views or foreign tables. MERGE follows SQL Standard per the most recent SQL:2016. Includes full tests and documentation, including full isolation tests to demonstrate the concurrent behavior. This version written from scratch in 2017 by Simon Riggs, using docs and tests originally written in 2009. Later work from Pavan Deolasee has been both complex and deep, leaving the lead author credit now in his hands. Extensive discussion of concurrency from Peter Geoghegan, with thanks for the time and effort contributed. Various issues reported via sqlsmith by Andreas Seltenreich Authors: Pavan Deolasee, Simon Riggs Reviewers: Peter Geoghegan, Amit Langote, Tomas Vondra, Simon Riggs Discussion: https://postgr.es/m/CANP8+jKitBSrB7oTgT9CY2i1ObfOt36z0XMraQc+Xrz8QB0nXA@mail.gmail.com https://postgr.es/m/CAH2-WzkJdBuxj9PO=2QaO9-3h3xGbQPZ34kJH=HukRekwM-GZg@mail.gmail.com --- doc/src/sgml/ref/merge.sgml | 603 ++++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 603 insertions(+) create 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 new file mode 100644 index 00000000000..a7d44a39b68 --- /dev/null +++ b/doc/src/sgml/ref/merge.sgml @@ -0,0 +1,603 @@ + + + + + + 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.3