diff options
Diffstat (limited to 'doc/src/sgml/man7/INSERT.7')
| -rw-r--r-- | doc/src/sgml/man7/INSERT.7 | 756 |
1 files changed, 756 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..596f738 --- /dev/null +++ b/doc/src/sgml/man7/INSERT.7 @@ -0,0 +1,756 @@ +'\" t +.\" Title: INSERT +.\" Author: The PostgreSQL Global Development Group +.\" Generator: DocBook XSL Stylesheets vsnapshot <http://docbook.sf.net/> +.\" Date: 2022 +.\" Manual: PostgreSQL 14.5 Documentation +.\" Source: PostgreSQL 14.5 +.\" Language: English +.\" +.TH "INSERT" "7" "2022" "PostgreSQL 14.5" "PostgreSQL 14.5 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\ \&62.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\&. +.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\&. +.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)\&. |