'\" t .\" Title: INSERT .\" Author: The PostgreSQL Global Development Group .\" Generator: DocBook XSL Stylesheets v1.79.1 .\" Date: 2019 .\" Manual: PostgreSQL 9.6.12 Documentation .\" Source: PostgreSQL 9.6.12 .\" Language: English .\" .TH "INSERT" "7" "2019" "PostgreSQL 9.6.12" "PostgreSQL 9.6.12 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 [, \&.\&.\&.] ) ] { 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 [, \&.\&.\&.] ) = ( { \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 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, \(lqWITH Queries (Common Table Expressions)\(rq, in the documentation 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 excluded, since that\*(Aqs also the name of the special table representing rows 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 DEFAULT VALUES .RS 4 All columns will be filled with their default values\&. .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\&. .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 rows 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\&. .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\&. If \fIcount\fR is exactly one, and the target table has OIDs, then \fIoid\fR is the OID assigned to the inserted row\&. The single row must have been inserted rather than updated\&. Otherwise \fIoid\fR is zero\&. .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 "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 .\} .sp .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 Possible limitations of the \fIquery\fR clause are documented under \fBSELECT\fR(7)\&.