Adding upstream version 16.2.upstream/16.2

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 766 insertions, 0 deletions

diff --git a/doc/src/sgml/man7/INSERT.7 b/doc/src/sgml/man7/INSERT.7
new file mode 100644
index 0000000..ad9b8f3
--- /dev/null
+++ b/doc/src/sgml/man7/INSERT.7
@@ -0,0 +1,766 @@
+'\" t
+.\"     Title: INSERT
+.\"    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 "INSERT" "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"
+INSERT \- create new rows in a table
+.SH "SYNOPSIS"
+.sp
+.nf
+[ WITH [ RECURSIVE ] \fIwith_query\fR [, \&.\&.\&.] ]
+INSERT INTO \fItable_name\fR [ AS \fIalias\fR ] [ ( \fIcolumn_name\fR [, \&.\&.\&.] ) ]
+    [ OVERRIDING { SYSTEM | USER } VALUE ]
+    { DEFAULT VALUES | VALUES ( { \fIexpression\fR | DEFAULT } [, \&.\&.\&.] ) [, \&.\&.\&.] | \fIquery\fR }
+    [ ON CONFLICT [ \fIconflict_target\fR ] \fIconflict_action\fR ]
+    [ RETURNING * | \fIoutput_expression\fR [ [ AS ] \fIoutput_name\fR ] [, \&.\&.\&.] ]
+
+where \fIconflict_target\fR can be one of:
+
+    ( { \fIindex_column_name\fR | ( \fIindex_expression\fR ) } [ COLLATE \fIcollation\fR ] [ \fIopclass\fR ] [, \&.\&.\&.] ) [ WHERE \fIindex_predicate\fR ]
+    ON CONSTRAINT \fIconstraint_name\fR
+
+and \fIconflict_action\fR is one of:
+
+    DO NOTHING
+    DO UPDATE SET { \fIcolumn_name\fR = { \fIexpression\fR | DEFAULT } |
+                    ( \fIcolumn_name\fR [, \&.\&.\&.] ) = [ ROW ] ( { \fIexpression\fR | DEFAULT } [, \&.\&.\&.] ) |
+                    ( \fIcolumn_name\fR [, \&.\&.\&.] ) = ( \fIsub\-SELECT\fR )
+                  } [, \&.\&.\&.]
+              [ WHERE \fIcondition\fR ]
+.fi
+.SH "DESCRIPTION"
+.PP
+\fBINSERT\fR
+inserts new rows into a table\&. One can insert one or more rows specified by value expressions, or zero or more rows resulting from a query\&.
+.PP
+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; or the first
+\fIN\fR
+column names, if there are only
+\fIN\fR
+columns supplied by the
+VALUES
+clause or
+\fIquery\fR\&. The values supplied by the
+VALUES
+clause or
+\fIquery\fR
+are associated with the explicit or implicit column list left\-to\-right\&.
+.PP
+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\&.
+.PP
+If the expression for any column is not of the correct data type, automatic type conversion will be attempted\&.
+.PP
+\fBINSERT\fR
+into tables that lack unique indexes will not be blocked by concurrent activity\&. Tables with unique indexes might block if concurrent sessions perform actions that lock or modify rows matching the unique index values being inserted; the details are covered in
+Section\ \&64.5\&.
+ON CONFLICT
+can be used to specify an alternative action to raising a unique constraint or exclusion constraint violation error\&. (See
+ON CONFLICT Clause
+below\&.)
+.PP
+The optional
+RETURNING
+clause causes
+\fBINSERT\fR
+to compute and return value(s) based on each row actually inserted (or updated, if an
+ON CONFLICT DO UPDATE
+clause was used)\&. This is primarily useful for obtaining values that were supplied by defaults, such as a serial sequence number\&. However, any expression using the table\*(Aqs columns is allowed\&. The syntax of the
+RETURNING
+list is identical to that of the output list of
+\fBSELECT\fR\&. Only rows that were successfully inserted or updated will be returned\&. For example, if a row was locked but not updated because an
+ON CONFLICT DO UPDATE \&.\&.\&. WHERE
+clause
+\fIcondition\fR
+was not satisfied, the row will not be returned\&.
+.PP
+You must have
+INSERT
+privilege on a table in order to insert into it\&. If
+ON CONFLICT DO UPDATE
+is present,
+UPDATE
+privilege on the table is also required\&.
+.PP
+If a column list is specified, you only need
+INSERT
+privilege on the listed columns\&. Similarly, when
+ON CONFLICT DO UPDATE
+is specified, you only need
+UPDATE
+privilege on the column(s) that are listed to be updated\&. However,
+ON CONFLICT DO UPDATE
+also requires
+SELECT
+privilege on any column whose values are read in the
+ON CONFLICT DO UPDATE
+expressions or
+\fIcondition\fR\&.
+.PP
+Use of the
+RETURNING
+clause requires
+SELECT
+privilege on all columns mentioned in
+RETURNING\&. If you use the
+\fIquery\fR
+clause to insert rows from a query, you of course need to have
+SELECT
+privilege on any table or column used in the query\&.
+.SH "PARAMETERS"
+.SS "Inserting"
+.PP
+This section covers parameters that may be used when only inserting new rows\&. Parameters
+\fIexclusively\fR
+used with the
+ON CONFLICT
+clause are described separately\&.
+.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
+\fBINSERT\fR
+query\&. See
+Section\ \&7.8
+and
+\fBSELECT\fR(7)
+for details\&.
+.sp
+It is possible for the
+\fIquery\fR
+(\fBSELECT\fR
+statement) to also contain a
+WITH
+clause\&. In such a case both sets of
+\fIwith_query\fR
+can be referenced within the
+\fIquery\fR, but the second one takes precedence since it is more closely nested\&.
+.RE
+.PP
+\fItable_name\fR
+.RS 4
+The name (optionally schema\-qualified) of an existing table\&.
+.RE
+.PP
+\fIalias\fR
+.RS 4
+A substitute name for
+\fItable_name\fR\&. When an alias is provided, it completely hides the actual name of the table\&. This is particularly useful when
+ON CONFLICT DO UPDATE
+targets a table named
+\fIexcluded\fR, since that will otherwise be taken as the name of the special table representing the row proposed for insertion\&.
+.RE
+.PP
+\fIcolumn_name\fR
+.RS 4
+The name of a column in the table named by
+\fItable_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\&.) When referencing a column with
+ON CONFLICT DO UPDATE, do not include the table\*(Aqs name in the specification of a target column\&. For example,
+INSERT INTO table_name \&.\&.\&. ON CONFLICT DO UPDATE SET table_name\&.col = 1
+is invalid (this follows the general behavior for
+\fBUPDATE\fR)\&.
+.RE
+.PP
+OVERRIDING SYSTEM VALUE
+.RS 4
+If this clause is specified, then any values supplied for identity columns will override the default sequence\-generated values\&.
+.sp
+For an identity column defined as
+GENERATED ALWAYS, it is an error to insert an explicit value (other than
+DEFAULT) without specifying either
+OVERRIDING SYSTEM VALUE
+or
+OVERRIDING USER VALUE\&. (For an identity column defined as
+GENERATED BY DEFAULT,
+OVERRIDING SYSTEM VALUE
+is the normal behavior and specifying it does nothing, but
+PostgreSQL
+allows it as an extension\&.)
+.RE
+.PP
+OVERRIDING USER VALUE
+.RS 4
+If this clause is specified, then any values supplied for identity columns are ignored and the default sequence\-generated values are applied\&.
+.sp
+This clause is useful for example when copying values between tables\&. Writing
+INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM tbl1
+will copy from
+tbl1
+all columns that are not identity columns in
+tbl2
+while values for the identity columns in
+tbl2
+will be generated by the sequences associated with
+tbl2\&.
+.RE
+.PP
+DEFAULT VALUES
+.RS 4
+All columns will be filled with their default values, as if
+DEFAULT
+were explicitly specified for each column\&. (An
+OVERRIDING
+clause is not permitted in this form\&.)
+.RE
+.PP
+\fIexpression\fR
+.RS 4
+An expression or value to assign to the corresponding column\&.
+.RE
+.PP
+DEFAULT
+.RS 4
+The corresponding column will be filled with its default value\&. An identity column will be filled with a new value generated by the associated sequence\&. For a generated column, specifying this is permitted but merely specifies the normal behavior of computing the column from its generation expression\&.
+.RE
+.PP
+\fIquery\fR
+.RS 4
+A query (\fBSELECT\fR
+statement) that supplies the rows to be inserted\&. Refer to the
+\fBSELECT\fR(7)
+statement for a description of the syntax\&.
+.RE
+.PP
+\fIoutput_expression\fR
+.RS 4
+An expression to be computed and returned by the
+\fBINSERT\fR
+command after each row is inserted or updated\&. The expression can use any column names of the table named by
+\fItable_name\fR\&. Write
+*
+to return all columns of the inserted or updated row(s)\&.
+.RE
+.PP
+\fIoutput_name\fR
+.RS 4
+A name to use for a returned column\&.
+.RE
+.SS "ON CONFLICT Clause"
+.PP
+The optional
+ON CONFLICT
+clause specifies an alternative action to raising a unique violation or exclusion constraint violation error\&. For each individual row proposed for insertion, either the insertion proceeds, or, if an
+\fIarbiter\fR
+constraint or index specified by
+\fIconflict_target\fR
+is violated, the alternative
+\fIconflict_action\fR
+is taken\&.
+ON CONFLICT DO NOTHING
+simply avoids inserting a row as its alternative action\&.
+ON CONFLICT DO UPDATE
+updates the existing row that conflicts with the row proposed for insertion as its alternative action\&.
+.PP
+\fIconflict_target\fR
+can perform
+\fIunique index inference\fR\&. When performing inference, it consists of one or more
+\fIindex_column_name\fR
+columns and/or
+\fIindex_expression\fR
+expressions, and an optional
+\fIindex_predicate\fR\&. All
+\fItable_name\fR
+unique indexes that, without regard to order, contain exactly the
+\fIconflict_target\fR\-specified columns/expressions are inferred (chosen) as arbiter indexes\&. If an
+\fIindex_predicate\fR
+is specified, it must, as a further requirement for inference, satisfy arbiter indexes\&. Note that this means a non\-partial unique index (a unique index without a predicate) will be inferred (and thus used by
+ON CONFLICT) if such an index satisfying every other criteria is available\&. If an attempt at inference is unsuccessful, an error is raised\&.
+.PP
+ON CONFLICT DO UPDATE
+guarantees an atomic
+\fBINSERT\fR
+or
+\fBUPDATE\fR
+outcome; provided there is no independent error, one of those two outcomes is guaranteed, even under high concurrency\&. This is also known as
+UPSERT
+\(em
+\(lqUPDATE or INSERT\(rq\&.
+.PP
+\fIconflict_target\fR
+.RS 4
+Specifies which conflicts
+ON CONFLICT
+takes the alternative action on by choosing
+arbiter indexes\&. Either performs
+\fIunique index inference\fR, or names a constraint explicitly\&. For
+ON CONFLICT DO NOTHING, it is optional to specify a
+\fIconflict_target\fR; when omitted, conflicts with all usable constraints (and unique indexes) are handled\&. For
+ON CONFLICT DO UPDATE, a
+\fIconflict_target\fR
+\fImust\fR
+be provided\&.
+.RE
+.PP
+\fIconflict_action\fR
+.RS 4
+\fIconflict_action\fR
+specifies an alternative
+ON CONFLICT
+action\&. It can be either
+DO NOTHING, or a
+DO UPDATE
+clause specifying the exact details of the
+UPDATE
+action to be performed in case of a conflict\&. The
+SET
+and
+WHERE
+clauses in
+ON CONFLICT DO UPDATE
+have access to the existing row using the table\*(Aqs name (or an alias), and to the row proposed for insertion using the special
+\fIexcluded\fR
+table\&.
+SELECT
+privilege is required on any column in the target table where corresponding
+\fIexcluded\fR
+columns are read\&.
+.sp
+Note that the effects of all per\-row
+BEFORE INSERT
+triggers are reflected in
+\fIexcluded\fR
+values, since those effects may have contributed to the row being excluded from insertion\&.
+.RE
+.PP
+\fIindex_column_name\fR
+.RS 4
+The name of a
+\fItable_name\fR
+column\&. Used to infer arbiter indexes\&. Follows
+\fBCREATE INDEX\fR
+format\&.
+SELECT
+privilege on
+\fIindex_column_name\fR
+is required\&.
+.RE
+.PP
+\fIindex_expression\fR
+.RS 4
+Similar to
+\fIindex_column_name\fR, but used to infer expressions on
+\fItable_name\fR
+columns appearing within index definitions (not simple columns)\&. Follows
+\fBCREATE INDEX\fR
+format\&.
+SELECT
+privilege on any column appearing within
+\fIindex_expression\fR
+is required\&.
+.RE
+.PP
+\fIcollation\fR
+.RS 4
+When specified, mandates that corresponding
+\fIindex_column_name\fR
+or
+\fIindex_expression\fR
+use a particular collation in order to be matched during inference\&. Typically this is omitted, as collations usually do not affect whether or not a constraint violation occurs\&. Follows
+\fBCREATE INDEX\fR
+format\&.
+.RE
+.PP
+\fIopclass\fR
+.RS 4
+When specified, mandates that corresponding
+\fIindex_column_name\fR
+or
+\fIindex_expression\fR
+use particular operator class in order to be matched during inference\&. Typically this is omitted, as the
+\fIequality\fR
+semantics are often equivalent across a type\*(Aqs operator classes anyway, or because it\*(Aqs sufficient to trust that the defined unique indexes have the pertinent definition of equality\&. Follows
+\fBCREATE INDEX\fR
+format\&.
+.RE
+.PP
+\fIindex_predicate\fR
+.RS 4
+Used to allow inference of partial unique indexes\&. Any indexes that satisfy the predicate (which need not actually be partial indexes) can be inferred\&. Follows
+\fBCREATE INDEX\fR
+format\&.
+SELECT
+privilege on any column appearing within
+\fIindex_predicate\fR
+is required\&.
+.RE
+.PP
+\fIconstraint_name\fR
+.RS 4
+Explicitly specifies an arbiter
+\fIconstraint\fR
+by name, rather than inferring a constraint or index\&.
+.RE
+.PP
+\fIcondition\fR
+.RS 4
+An expression that returns a value of type
+boolean\&. Only rows for which this expression returns
+true
+will be updated, although all rows will be locked when the
+ON CONFLICT DO UPDATE
+action is taken\&. Note that
+\fIcondition\fR
+is evaluated last, after a conflict has been identified as a candidate to update\&.
+.RE
+.PP
+Note that exclusion constraints are not supported as arbiters with
+ON CONFLICT DO UPDATE\&. In all cases, only
+NOT DEFERRABLE
+constraints and unique indexes are supported as arbiters\&.
+.PP
+\fBINSERT\fR
+with an
+ON CONFLICT DO UPDATE
+clause is a
+\(lqdeterministic\(rq
+statement\&. This means that the command will not be allowed to affect any single existing row more than once; a cardinality violation error will be raised when this situation arises\&. Rows proposed for insertion should not duplicate each other in terms of attributes constrained by an arbiter index or constraint\&.
+.PP
+Note that it is currently not supported for the
+ON CONFLICT DO UPDATE
+clause of an
+\fBINSERT\fR
+applied to a partitioned table to update the partition key of a conflicting row such that it requires the row be moved to a new partition\&.
+.if n \{\
+.sp
+.\}
+.RS 4
+.it 1 an-trap
+.nr an-no-space-flag 1
+.nr an-break-flag 1
+.br
+.ps +1
+\fBTip\fR
+.ps -1
+.br
+.PP
+It is often preferable to use unique index inference rather than naming a constraint directly using
+ON CONFLICT ON CONSTRAINT
+\fI constraint_name\fR\&. Inference will continue to work correctly when the underlying index is replaced by another more or less equivalent index in an overlapping way, for example when using
+CREATE UNIQUE INDEX \&.\&.\&. CONCURRENTLY
+before dropping the index being replaced\&.
+.sp .5v
+.RE
+.SH "OUTPUTS"
+.PP
+On successful completion, an
+\fBINSERT\fR
+command returns a command tag of the form
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+INSERT \fIoid\fR \fIcount\fR
+.fi
+.if n \{\
+.RE
+.\}
+.sp
+The
+\fIcount\fR
+is the number of rows inserted or updated\&.
+\fIoid\fR
+is always 0 (it used to be the
+OID
+assigned to the inserted row if
+\fIcount\fR
+was exactly one and the target table was declared
+WITH OIDS
+and 0 otherwise, but creating a table
+WITH OIDS
+is not supported anymore)\&.
+.PP
+If the
+\fBINSERT\fR
+command contains a
+RETURNING
+clause, the result will be similar to that of a
+\fBSELECT\fR
+statement containing the columns and values defined in the
+RETURNING
+list, computed over the row(s) inserted or updated by the command\&.
+.SH "NOTES"
+.PP
+If the specified table is a partitioned table, each row is routed to the appropriate partition and inserted into it\&. If the specified table is a partition, an error will occur if one of the input rows violates the partition constraint\&.
+.PP
+You may also wish to consider using
+\fBMERGE\fR, since that allows mixing
+\fBINSERT\fR,
+\fBUPDATE\fR, and
+\fBDELETE\fR
+within a single statement\&. See
+\fBMERGE\fR(7)\&.
+.SH "EXAMPLES"
+.PP
+Insert a single row into table
+films:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+INSERT INTO films VALUES
+    (\*(AqUA502\*(Aq, \*(AqBananas\*(Aq, 105, \*(Aq1971\-07\-13\*(Aq, \*(AqComedy\*(Aq, \*(Aq82 minutes\*(Aq);
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+In this example, the
+len
+column is omitted and therefore it will have the default value:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+INSERT INTO films (code, title, did, date_prod, kind)
+    VALUES (\*(AqT_601\*(Aq, \*(AqYojimbo\*(Aq, 106, \*(Aq1961\-06\-16\*(Aq, \*(AqDrama\*(Aq);
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+This example uses the
+DEFAULT
+clause for the date columns rather than specifying a value:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+INSERT INTO films VALUES
+    (\*(AqUA502\*(Aq, \*(AqBananas\*(Aq, 105, DEFAULT, \*(AqComedy\*(Aq, \*(Aq82 minutes\*(Aq);
+INSERT INTO films (code, title, did, date_prod, kind)
+    VALUES (\*(AqT_601\*(Aq, \*(AqYojimbo\*(Aq, 106, DEFAULT, \*(AqDrama\*(Aq);
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+To insert a row consisting entirely of default values:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+INSERT INTO films DEFAULT VALUES;
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+To insert multiple rows using the multirow
+\fBVALUES\fR
+syntax:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+INSERT INTO films (code, title, did, date_prod, kind) VALUES
+    (\*(AqB6717\*(Aq, \*(AqTampopo\*(Aq, 110, \*(Aq1985\-02\-10\*(Aq, \*(AqComedy\*(Aq),
+    (\*(AqHG120\*(Aq, \*(AqThe Dinner Game\*(Aq, 140, DEFAULT, \*(AqComedy\*(Aq);
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+This example inserts some rows into table
+films
+from a table
+tmp_films
+with the same column layout as
+films:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+INSERT INTO films SELECT * FROM tmp_films WHERE date_prod < \*(Aq2004\-05\-07\*(Aq;
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+This example inserts into array columns:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+\-\- Create an empty 3x3 gameboard for noughts\-and\-crosses
+INSERT INTO tictactoe (game, board[1:3][1:3])
+    VALUES (1, \*(Aq{{" "," "," "},{" "," "," "},{" "," "," "}}\*(Aq);
+\-\- The subscripts in the above example aren\*(Aqt really needed
+INSERT INTO tictactoe (game, board)
+    VALUES (2, \*(Aq{{X," "," "},{" ",O," "},{" ",X," "}}\*(Aq);
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+Insert a single row into table
+distributors, returning the sequence number generated by the
+DEFAULT
+clause:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+INSERT INTO distributors (did, dname) VALUES (DEFAULT, \*(AqXYZ Widgets\*(Aq)
+   RETURNING did;
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+Increment the sales count of the salesperson who manages the account for Acme Corporation, and record the whole updated row along with current time in a log table:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+WITH upd AS (
+  UPDATE employees SET sales_count = sales_count + 1 WHERE id =
+    (SELECT sales_person FROM accounts WHERE name = \*(AqAcme Corporation\*(Aq)
+    RETURNING *
+)
+INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+Insert or update new distributors as appropriate\&. Assumes a unique index has been defined that constrains values appearing in the
+did
+column\&. Note that the special
+\fIexcluded\fR
+table is used to reference values originally proposed for insertion:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+INSERT INTO distributors (did, dname)
+    VALUES (5, \*(AqGizmo Transglobal\*(Aq), (6, \*(AqAssociated Computing, Inc\*(Aq)
+    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED\&.dname;
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+Insert a distributor, or do nothing for rows proposed for insertion when an existing, excluded row (a row with a matching constrained column or columns after before row insert triggers fire) exists\&. Example assumes a unique index has been defined that constrains values appearing in the
+did
+column:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+INSERT INTO distributors (did, dname) VALUES (7, \*(AqRedline GmbH\*(Aq)
+    ON CONFLICT (did) DO NOTHING;
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+Insert or update new distributors as appropriate\&. Example assumes a unique index has been defined that constrains values appearing in the
+did
+column\&.
+WHERE
+clause is used to limit the rows actually updated (any existing row not updated will still be locked, though):
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+\-\- Don\*(Aqt update existing distributors based in a certain ZIP code
+INSERT INTO distributors AS d (did, dname) VALUES (8, \*(AqAnvil Distribution\*(Aq)
+    ON CONFLICT (did) DO UPDATE
+    SET dname = EXCLUDED\&.dname || \*(Aq (formerly \*(Aq || d\&.dname || \*(Aq)\*(Aq
+    WHERE d\&.zipcode <> \*(Aq21201\*(Aq;
+
+\-\- Name a constraint directly in the statement (uses associated
+\-\- index to arbitrate taking the DO NOTHING action)
+INSERT INTO distributors (did, dname) VALUES (9, \*(AqAntwerp Design\*(Aq)
+    ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING;
+.fi
+.if n \{\
+.RE
+.\}
+.PP
+Insert new distributor if possible; otherwise
+DO NOTHING\&. Example assumes a unique index has been defined that constrains values appearing in the
+did
+column on a subset of rows where the
+is_active
+Boolean column evaluates to
+true:
+.sp
+.if n \{\
+.RS 4
+.\}
+.nf
+\-\- This statement could infer a partial unique index on "did"
+\-\- with a predicate of "WHERE is_active", but it could also
+\-\- just use a regular unique constraint on "did"
+INSERT INTO distributors (did, dname) VALUES (10, \*(AqConrad International\*(Aq)
+    ON CONFLICT (did) WHERE is_active DO NOTHING;
+.fi
+.if n \{\
+.RE
+.\}
+.SH "COMPATIBILITY"
+.PP
+\fBINSERT\fR
+conforms to the SQL standard, except that the
+RETURNING
+clause is a
+PostgreSQL
+extension, as is the ability to use
+WITH
+with
+\fBINSERT\fR, and the ability to specify an alternative action with
+ON CONFLICT\&. Also, the case in which a column name list is omitted, but not all the columns are filled from the
+VALUES
+clause or
+\fIquery\fR, is disallowed by the standard\&. If you prefer a more SQL standard conforming statement than
+ON CONFLICT, see
+\fBMERGE\fR(7)\&.
+.PP
+The SQL standard specifies that
+OVERRIDING SYSTEM VALUE
+can only be specified if an identity column that is generated always exists\&. PostgreSQL allows the clause in any case and ignores it if it is not applicable\&.
+.PP
+Possible limitations of the
+\fIquery\fR
+clause are documented under
+\fBSELECT\fR(7)\&.