summaryrefslogtreecommitdiffstats
path: root/doc/src/sgml/man7/MERGE.7
diff options
context:
space:
mode:
Diffstat (limited to '')
-rw-r--r--doc/src/sgml/man7/MERGE.7122
1 files changed, 58 insertions, 64 deletions
diff --git a/doc/src/sgml/man7/MERGE.7 b/doc/src/sgml/man7/MERGE.7
index 65ff5c4..87bd747 100644
--- a/doc/src/sgml/man7/MERGE.7
+++ b/doc/src/sgml/man7/MERGE.7
@@ -3,11 +3,11 @@
.\" Author: The PostgreSQL Global Development Group
.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/>
.\" Date: 2024
-.\" Manual: PostgreSQL 15.6 Documentation
-.\" Source: PostgreSQL 15.6
+.\" Manual: PostgreSQL 15.7 Documentation
+.\" Source: PostgreSQL 15.7
.\" Language: English
.\"
-.TH "MERGE" "7" "2024" "PostgreSQL 15.6" "PostgreSQL 15.6 Documentation"
+.TH "MERGE" "7" "2024" "PostgreSQL 15.7" "PostgreSQL 15.7 Documentation"
.\" -----------------------------------------------------------------
.\" * Define some portability stuff
.\" -----------------------------------------------------------------
@@ -55,7 +55,9 @@ INSERT [( \fIcolumn_name\fR [, \&.\&.\&.] )]
and \fImerge_update\fR is:
UPDATE SET { \fIcolumn_name\fR = { \fIexpression\fR | DEFAULT } |
- ( \fIcolumn_name\fR [, \&.\&.\&.] ) = ( { \fIexpression\fR | DEFAULT } [, \&.\&.\&.] ) } [, \&.\&.\&.]
+ ( \fIcolumn_name\fR [, \&.\&.\&.] ) = [ ROW ] ( { \fIexpression\fR | DEFAULT } [, \&.\&.\&.] ) |
+ ( \fIcolumn_name\fR [, \&.\&.\&.] ) = ( \fIsub\-SELECT\fR )
+ } [, \&.\&.\&.]
and \fImerge_delete\fR is:
@@ -64,7 +66,7 @@ DELETE
.SH "DESCRIPTION"
.PP
\fBMERGE\fR
-performs actions that modify rows in the
+performs actions that modify rows in the target table identified as
\fItarget_table_name\fR, using the
\fIdata_source\fR\&.
\fBMERGE\fR
@@ -81,9 +83,7 @@ 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
+to the target table producing zero or more candidate change rows\&. For each candidate change row, the status of
MATCHED
or
NOT MATCHED
@@ -100,8 +100,7 @@ actions have the same effect as regular
\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\&.
+clause and no table name is specified\&. All actions refer to the target table, though modifications to other tables may be made using triggers\&.
.PP
When
DO NOTHING
@@ -113,35 +112,48 @@ 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
+privilege on the column(s) of the target table 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
+privilege on the target table\&. 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
+privilege on the target table\&. If you specify a
+DO NOTHING
+action, you must have the
+SELECT
+privilege on at least one column of the target table\&. You will also need
SELECT
privilege on any column(s) of the
\fIdata_source\fR
-and
-\fItarget_table_name\fR
-referred to in any
+and of the target table referred to in any
condition
-or
-expression\&.
+(including
+join_condition) or
+expression\&. Privileges are tested once at statement start and are checked whether or not particular
+WHEN
+clauses are executed\&.
.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\&.
+is not supported if the target table is a materialized view, foreign table, or if it has any rules defined on it\&.
.SH "PARAMETERS"
.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\&. Note that
+WITH RECURSIVE
+is not supported by
+\fBMERGE\fR\&.
+.RE
+.PP
\fItarget_table_name\fR
.RS 4
The name (optionally schema\-qualified) of the target table to merge into\&. If
@@ -184,8 +196,7 @@ can be specified after the table name to explicitly indicate that descendant tab
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
+statement) that supplies the rows to be merged into the target table\&. Refer to the
\fBSELECT\fR(7)
statement or
\fBVALUES\fR(7)
@@ -206,8 +217,7 @@ boolean
WHERE
clause) that specifies which rows in the
\fIdata_source\fR
-match rows in the
-\fItarget_table_name\fR\&.
+match rows in the target table\&.
.if n \{\
.sp
.\}
@@ -220,16 +230,12 @@ match rows in the
\fBWarning\fR
.ps -1
.br
-Only columns from
-\fItarget_table_name\fR
-that attempt to match
+Only columns from the target table 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\&.
+subexpressions that only reference the target table\*(Aqs columns can affect which action is taken, often in surprising ways\&.
.sp .5v
.RE
.RE
@@ -244,8 +250,7 @@ If the
WHEN
clause specifies
WHEN MATCHED
-and the candidate change row matches a row in the
-\fItarget_table_name\fR, the
+and the candidate change row matches a row in the target table, the
WHEN
clause is executed if the
\fIcondition\fR
@@ -256,8 +261,7 @@ 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
+and the candidate change row does not match a row in the target table, the
WHEN
clause is executed if the
\fIcondition\fR
@@ -288,11 +292,7 @@ action that inserts one row into the target table\&. The target column names can
.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\&.
+If the target table is a partitioned table, each row is routed to the appropriate partition and inserted into it\&. If the target table 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
@@ -309,8 +309,7 @@ clause can only refer to columns from the source relation, since by definition t
.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\&.
+action that updates the current row of the target table\&. Column names may not be specified more than once\&.
.sp
Neither a table name nor a
WHERE
@@ -321,16 +320,14 @@ clause are allowed\&.
.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
+action that deletes the current row of the target table\&. 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\&.
+The name of a column in the target table\&. 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
@@ -359,11 +356,12 @@ clause is not permitted in this form\&.)
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
+\fIdata_source\fR
row\&. If used in a
WHEN NOT MATCHED
clause, the expression can use values from the
-data_source\&.
+\fIdata_source\fR
+row\&.
.RE
.PP
DEFAULT
@@ -373,17 +371,13 @@ NULL
if no specific default expression has been assigned to it)\&.
.RE
.PP
-\fIwith_query\fR
+\fIsub\-SELECT\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\&.
+A
+SELECT
+sub\-query that produces as many output columns as are listed in the parenthesized column list preceding it\&. The sub\-query must yield no more than one row when executed\&. If it yields one row, its column values are assigned to the target columns; if it yields no rows, NULL values are assigned to the target columns\&. The sub\-query can refer to values from the original row in the target table, and values from the
+\fIdata_source\fR
+row\&.
.RE
.SH "OUTPUTS"
.PP