diff options
Diffstat (limited to 'doc/src/sgml/man7/MERGE.7')
| -rw-r--r-- | doc/src/sgml/man7/MERGE.7 | 678 |
1 files changed, 678 insertions, 0 deletions
diff --git a/doc/src/sgml/man7/MERGE.7 b/doc/src/sgml/man7/MERGE.7 new file mode 100644 index 0000000..09362ea --- /dev/null +++ b/doc/src/sgml/man7/MERGE.7 @@ -0,0 +1,678 @@ +'\" t +.\" Title: MERGE +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2024 +.\" Manual: PostgreSQL 16.2 Documentation +.\" Source: PostgreSQL 16.2 +.\" Language: English +.\" +.TH "MERGE" "7" "2024" "PostgreSQL 16.2" "PostgreSQL 16.2 Documentation" +.\" ----------------------------------------------------------------- +.\" * Define some portability stuff +.\" ----------------------------------------------------------------- +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.\" http://bugs.debian.org/507673 +.\" http://lists.gnu.org/archive/html/groff/2009-02/msg00013.html +.\" ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ +.ie \n(.g .ds Aq \(aq +.el .ds Aq ' +.\" ----------------------------------------------------------------- +.\" * set default formatting +.\" ----------------------------------------------------------------- +.\" disable hyphenation +.nh +.\" disable justification (adjust text to left margin only) +.ad l +.\" ----------------------------------------------------------------- +.\" * MAIN CONTENT STARTS HERE * +.\" ----------------------------------------------------------------- +.SH "NAME" +MERGE \- conditionally insert, update, or delete rows of a table +.SH "SYNOPSIS" +.sp +.nf +[ WITH \fIwith_query\fR [, \&.\&.\&.] ] +MERGE INTO [ ONLY ] \fItarget_table_name\fR [ * ] [ [ AS ] \fItarget_alias\fR ] +USING \fIdata_source\fR ON \fIjoin_condition\fR +\fIwhen_clause\fR [\&.\&.\&.] + +where \fIdata_source\fR is: + +{ [ ONLY ] \fIsource_table_name\fR [ * ] | ( \fIsource_query\fR ) } [ [ AS ] \fIsource_alias\fR ] + +and \fIwhen_clause\fR is: + +{ WHEN MATCHED [ AND \fIcondition\fR ] THEN { \fImerge_update\fR | \fImerge_delete\fR | DO NOTHING } | + WHEN NOT MATCHED [ AND \fIcondition\fR ] THEN { \fImerge_insert\fR | DO NOTHING } } + +and \fImerge_insert\fR is: + +INSERT [( \fIcolumn_name\fR [, \&.\&.\&.] )] +[ OVERRIDING { SYSTEM | USER } VALUE ] +{ VALUES ( { \fIexpression\fR | DEFAULT } [, \&.\&.\&.] ) | DEFAULT VALUES } + +and \fImerge_update\fR is: + +UPDATE SET { \fIcolumn_name\fR = { \fIexpression\fR | DEFAULT } | + ( \fIcolumn_name\fR [, \&.\&.\&.] ) = ( { \fIexpression\fR | DEFAULT } [, \&.\&.\&.] ) } [, \&.\&.\&.] + +and \fImerge_delete\fR is: + +DELETE +.fi +.SH "DESCRIPTION" +.PP +\fBMERGE\fR +performs actions that modify rows in the +\fItarget_table_name\fR, using the +\fIdata_source\fR\&. +\fBMERGE\fR +provides a single +SQL +statement that can conditionally +\fBINSERT\fR, +\fBUPDATE\fR +or +\fBDELETE\fR +rows, a task that would otherwise require multiple procedural language statements\&. +.PP +First, the +\fBMERGE\fR +command performs a join from +\fIdata_source\fR +to +\fItarget_table_name\fR +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\&. For each candidate change row, the first clause to evaluate as true is executed\&. No more than one +WHEN +clause is executed for any candidate change row\&. +.PP +\fBMERGE\fR +actions have the same effect as regular +\fBUPDATE\fR, +\fBINSERT\fR, or +\fBDELETE\fR +commands of the same names\&. The syntax of those commands is different, notably that there is no +WHERE +clause and no table name is specified\&. All actions refer to the +\fItarget_table_name\fR, though modifications to other tables may be made using triggers\&. +.PP +When +DO NOTHING +is specified, the source row is skipped\&. Since actions are evaluated in their specified order, +DO NOTHING +can be handy to skip non\-interesting source rows before more fine\-grained handling\&. +.PP +There is no separate +MERGE +privilege\&. If you specify an update action, you must have the +UPDATE +privilege on the column(s) of the +\fItarget_table_name\fR +that are referred to in the +SET +clause\&. If you specify an insert action, you must have the +INSERT +privilege on the +\fItarget_table_name\fR\&. If you specify a delete action, you must have the +DELETE +privilege on the +\fItarget_table_name\fR\&. Privileges are tested once at statement start and are checked whether or not particular +WHEN +clauses are executed\&. You will require the +SELECT +privilege on any column(s) of the +\fIdata_source\fR +and +\fItarget_table_name\fR +referred to in any +condition +or +expression\&. +.PP +\fBMERGE\fR +is not supported if the +\fItarget_table_name\fR +is a materialized view, foreign table, or if it has any rules defined on it\&. +.SH "PARAMETERS" +.PP +\fItarget_table_name\fR +.RS 4 +The name (optionally schema\-qualified) of the target table to merge into\&. If +ONLY +is specified before the table name, matching rows are updated or deleted in the named table only\&. If +ONLY +is not specified, matching rows are also updated or deleted in any tables inheriting from the named table\&. Optionally, +* +can be specified after the table name to explicitly indicate that descendant tables are included\&. The +ONLY +keyword and +* +option do not affect insert actions, which always insert into the named table only\&. +.RE +.PP +\fItarget_alias\fR +.RS 4 +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 INTO foo AS f, the remainder of the +\fBMERGE\fR +statement must refer to this table as +f +not +foo\&. +.RE +.PP +\fIsource_table_name\fR +.RS 4 +The name (optionally schema\-qualified) of the source table, view, or transition table\&. If +ONLY +is specified before the table name, matching rows are included from the named table only\&. If +ONLY +is not specified, matching rows are also included from any tables inheriting from the named table\&. Optionally, +* +can be specified after the table name to explicitly indicate that descendant tables are included\&. +.RE +.PP +\fIsource_query\fR +.RS 4 +A query (\fBSELECT\fR +statement or +\fBVALUES\fR +statement) that supplies the rows to be merged into the +\fItarget_table_name\fR\&. Refer to the +\fBSELECT\fR(7) +statement or +\fBVALUES\fR(7) +statement for a description of the syntax\&. +.RE +.PP +\fIsource_alias\fR +.RS 4 +A substitute name for the data source\&. When an alias is provided, it completely hides the actual name of the table or the fact that a query was issued\&. +.RE +.PP +\fIjoin_condition\fR +.RS 4 +\fIjoin_condition\fR +is an expression resulting in a value of type +boolean +(similar to a +WHERE +clause) that specifies which rows in the +\fIdata_source\fR +match rows in the +\fItarget_table_name\fR\&. +.if n \{\ +.sp +.\} +.RS 4 +.it 1 an-trap +.nr an-no-space-flag 1 +.nr an-break-flag 1 +.br +.ps +1 +\fBWarning\fR +.ps -1 +.br +Only columns from +\fItarget_table_name\fR +that attempt to match +\fIdata_source\fR +rows should appear in +\fIjoin_condition\fR\&. +\fIjoin_condition\fR +subexpressions that only reference +\fItarget_table_name\fR +columns can affect which action is taken, often in surprising ways\&. +.sp .5v +.RE +.RE +.PP +\fIwhen_clause\fR +.RS 4 +At least one +WHEN +clause is required\&. +.sp +If the +WHEN +clause specifies +WHEN MATCHED +and the candidate change row matches a row in the +\fItarget_table_name\fR, the +WHEN +clause is executed if the +\fIcondition\fR +is absent or it evaluates to +true\&. +.sp +Conversely, if the +WHEN +clause specifies +WHEN NOT MATCHED +and the candidate change row does not match a row in the +\fItarget_table_name\fR, the +WHEN +clause is executed if the +\fIcondition\fR +is absent or it evaluates to +true\&. +.RE +.PP +\fIcondition\fR +.RS 4 +An expression that returns a value of type +boolean\&. If this expression for a +WHEN +clause returns +true, then the action for that clause is executed for that row\&. +.sp +A condition on a +WHEN MATCHED +clause can refer to columns in both the source and the target relations\&. 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\&. +.RE +.PP +\fImerge_insert\fR +.RS 4 +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\&. +.sp +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\&. +.sp +If +\fItarget_table_name\fR +is a partitioned table, each row is routed to the appropriate partition and inserted into it\&. If +\fItarget_table_name\fR +is a partition, an error will occur if any input row violates the partition constraint\&. +.sp +Column names may not be specified more than once\&. +\fBINSERT\fR +actions cannot contain sub\-selects\&. +.sp +Only one +VALUES +clause can be specified\&. The +VALUES +clause can only refer to columns from the source relation, since by definition there is no matching target row\&. +.RE +.PP +\fImerge_update\fR +.RS 4 +The specification of an +UPDATE +action that updates the current row of the +\fItarget_table_name\fR\&. Column names may not be specified more than once\&. +.sp +Neither a table name nor a +WHERE +clause are allowed\&. +.RE +.PP +\fImerge_delete\fR +.RS 4 +Specifies a +DELETE +action that deletes the current row of the +\fItarget_table_name\fR\&. Do not include the table name or any other clauses, as you would normally do with a +\fBDELETE\fR(7) +command\&. +.RE +.PP +\fIcolumn_name\fR +.RS 4 +The name of a column in the +\fItarget_table_name\fR\&. 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\&.) Do not include the table\*(Aqs name in the specification of a target column\&. +.RE +.PP +OVERRIDING SYSTEM VALUE +.RS 4 +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\&. +.RE +.PP +OVERRIDING USER VALUE +.RS 4 +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\&. +.RE +.PP +DEFAULT VALUES +.RS 4 +All columns will be filled with their default values\&. (An +OVERRIDING +clause is not permitted in this form\&.) +.RE +.PP +\fIexpression\fR +.RS 4 +An expression to assign to the column\&. If used in a +WHEN MATCHED +clause, the expression can use values from the original row in the target table, and values from the +data_source +row\&. If used in a +WHEN NOT MATCHED +clause, the expression can use values from the +data_source\&. +.RE +.PP +DEFAULT +.RS 4 +Set the column to its default value (which will be +NULL +if no specific default expression has been assigned to it)\&. +.RE +.PP +\fIwith_query\fR +.RS 4 +The +WITH +clause allows you to specify one or more subqueries that can be referenced by name in the +\fBMERGE\fR +query\&. See +Section\ \&7.8 +and +\fBSELECT\fR(7) +for details\&. +.RE +.SH "OUTPUTS" +.PP +On successful completion, a +\fBMERGE\fR +command returns a command tag of the form +.sp +.if n \{\ +.RS 4 +.\} +.nf +MERGE \fItotal_count\fR +.fi +.if n \{\ +.RE +.\} +.sp +The +\fItotal_count\fR +is the total number of rows changed (whether inserted, updated, or deleted)\&. If +\fItotal_count\fR +is 0, no rows were changed in any way\&. +.SH "NOTES" +.PP +The following steps take place during the execution of +\fBMERGE\fR\&. +.sp +.RS 4 +.ie n \{\ +\h'-04' 1.\h'+01'\c +.\} +.el \{\ +.sp -1 +.IP " 1." 4.2 +.\} +Perform any +BEFORE STATEMENT +triggers for all actions specified, whether or not their +WHEN +clauses match\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04' 2.\h'+01'\c +.\} +.el \{\ +.sp -1 +.IP " 2." 4.2 +.\} +Perform a join from source to target table\&. The resulting query will be optimized normally and will produce a set of candidate change rows\&. For each candidate change row, +.sp +.RS 4 +.ie n \{\ +\h'-04' 1.\h'+01'\c +.\} +.el \{\ +.sp -1 +.IP " 1." 4.2 +.\} +Evaluate whether each row is +MATCHED +or +NOT MATCHED\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04' 2.\h'+01'\c +.\} +.el \{\ +.sp -1 +.IP " 2." 4.2 +.\} +Test each +WHEN +condition in the order specified until one returns true\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04' 3.\h'+01'\c +.\} +.el \{\ +.sp -1 +.IP " 3." 4.2 +.\} +When a condition returns true, perform the following actions: +.sp +.RS 4 +.ie n \{\ +\h'-04' 1.\h'+01'\c +.\} +.el \{\ +.sp -1 +.IP " 1." 4.2 +.\} +Perform any +BEFORE ROW +triggers that fire for the action\*(Aqs event type\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04' 2.\h'+01'\c +.\} +.el \{\ +.sp -1 +.IP " 2." 4.2 +.\} +Perform the specified action, invoking any check constraints on the target table\&. +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04' 3.\h'+01'\c +.\} +.el \{\ +.sp -1 +.IP " 3." 4.2 +.\} +Perform any +AFTER ROW +triggers that fire for the action\*(Aqs event type\&. +.RE +.RE +.RE +.sp +.RS 4 +.ie n \{\ +\h'-04' 3.\h'+01'\c +.\} +.el \{\ +.sp -1 +.IP " 3." 4.2 +.\} +Perform any +AFTER STATEMENT +triggers for actions specified, whether or not they actually occur\&. This is similar to the behavior of an +\fBUPDATE\fR +statement that modifies no rows\&. +.RE +.sp +In summary, statement triggers for an event type (say, +\fBINSERT\fR) will be fired whenever we +\fIspecify\fR +an action of that kind\&. In contrast, row\-level triggers will fire only for the specific event type being +\fIexecuted\fR\&. So a +\fBMERGE\fR +command might fire statement triggers for both +\fBUPDATE\fR +and +\fBINSERT\fR, even though only +\fBUPDATE\fR +row triggers were fired\&. +.PP +You should ensure that the join produces at most one candidate change row for each target row\&. In other words, a target row shouldn\*(Aqt 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 the row will cause an error\&. This can also occur if row triggers make changes to the target table and the rows so modified are then subsequently also modified by +\fBMERGE\fR\&. If the repeated action is an +\fBINSERT\fR, this will cause a uniqueness violation, while a repeated +\fBUPDATE\fR +or +\fBDELETE\fR +will cause a cardinality violation; the latter behavior is required by the +SQL +standard\&. This differs from historical +PostgreSQL +behavior of joins in +\fBUPDATE\fR +and +\fBDELETE\fR +statements where second and subsequent attempts to modify the same row are simply ignored\&. +.PP +If a +WHEN +clause omits an +AND +sub\-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 no final reachable clause is specified of either kind, it is possible that no action will be taken for a candidate change row\&. +.PP +The order in which rows are generated from the data source is indeterminate by default\&. A +\fIsource_query\fR +can be used to specify a consistent ordering, if required, which might be needed to avoid deadlocks between concurrent transactions\&. +.PP +There is no +RETURNING +clause with +\fBMERGE\fR\&. Actions of +\fBINSERT\fR, +\fBUPDATE\fR +and +\fBDELETE\fR +cannot contain +RETURNING +or +WITH +clauses\&. +.PP +When +\fBMERGE\fR +is run concurrently with other commands that modify the target table, the usual transaction isolation rules apply; see +Section\ \&13.2 +for an explanation on the behavior at each isolation level\&. You may also wish to consider using +\fBINSERT \&.\&.\&. ON CONFLICT\fR +as an alternative statement which offers the ability to run an +\fBUPDATE\fR +if a concurrent +\fBINSERT\fR +occurs\&. There are a variety of differences and restrictions between the two statement types and they are not interchangeable\&. +.SH "EXAMPLES" +.PP +Perform maintenance on +customer_accounts +based upon new +recent_transactions\&. +.sp +.if n \{\ +.RS 4 +.\} +.nf +MERGE INTO customer_account ca +USING recent_transactions t +ON t\&.customer_id = ca\&.customer_id +WHEN MATCHED THEN + UPDATE SET balance = balance + transaction_value +WHEN NOT MATCHED THEN + INSERT (customer_id, balance) + VALUES (t\&.customer_id, t\&.transaction_value); +.fi +.if n \{\ +.RE +.\} +.PP +Notice that this would be exactly equivalent to the following statement because the +MATCHED +result does not change during execution\&. +.sp +.if n \{\ +.RS 4 +.\} +.nf +MERGE INTO customer_account ca +USING (SELECT customer_id, transaction_value FROM recent_transactions) AS t +ON t\&.customer_id = ca\&.customer_id +WHEN MATCHED THEN + UPDATE SET balance = balance + transaction_value +WHEN NOT MATCHED THEN + INSERT (customer_id, balance) + VALUES (t\&.customer_id, t\&.transaction_value); +.fi +.if n \{\ +.RE +.\} +.PP +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\*(Aqt allow entries that have zero stock\&. +.sp +.if n \{\ +.RS 4 +.\} +.nf +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; +.fi +.if n \{\ +.RE +.\} +.sp +The +wine_stock_changes +table might be, for example, a temporary table recently loaded into the database\&. +.SH "COMPATIBILITY" +.PP +This command conforms to the +SQL +standard\&. +.PP +The +WITH +clause and +DO NOTHING +action are extensions to the +SQL +standard\&. |