diff options
Diffstat (limited to 'doc/src/sgml/ref/insert.sgml')
-rw-r--r-- | doc/src/sgml/ref/insert.sgml | 403 |
1 files changed, 385 insertions, 18 deletions
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index a3cccb9f7c..c88d1b7b50 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -22,9 +22,24 @@ PostgreSQL documentation <refsynopsisdiv> <synopsis> [ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ] -INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ] +INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ] [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ] { DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> } + [ ON CONFLICT [ <replaceable class="parameter">conflict_target</replaceable> ] <replaceable class="parameter">conflict_action</replaceable> ] [ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ] + +<phrase>where <replaceable class="parameter">conflict_target</replaceable> can be one of:</phrase> + + ( { <replaceable class="parameter">column_name_index</replaceable> | ( <replaceable class="parameter">expression_index</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> ] [, ...] ) [ WHERE <replaceable class="PARAMETER">index_predicate</replaceable> ] + ON CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> + +<phrase>and <replaceable class="parameter">conflict_action</replaceable> is one of:</phrase> + + DO NOTHING + DO UPDATE SET { <replaceable class="PARAMETER">column_name</replaceable> = { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } | + ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) | + ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) = ( <replaceable class="PARAMETER">sub-SELECT</replaceable> ) + } [, ...] + [ WHERE <replaceable class="PARAMETER">condition</replaceable> ] </synopsis> </refsynopsisdiv> @@ -59,19 +74,46 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace </para> <para> + <literal>ON CONFLICT</> can be used to specify an alternative + action to raising a unique constraint or exclusion constraint + violation error . (See <xref linkend="sql-on-conflict" + endterm="sql-on-conflict-title"> below.) + </para> + + <para> The optional <literal>RETURNING</> clause causes <command>INSERT</> - to compute and return value(s) based on each row actually inserted. - This is primarily useful for obtaining values that were supplied by - defaults, such as a serial sequence number. However, any expression - using the table's columns is allowed. The syntax of the - <literal>RETURNING</> list is identical to that of the output list - of <command>SELECT</>. + to compute and return value(s) based on each row actually inserted + (or updated, if an <literal>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's columns is allowed. The syntax of + the <literal>RETURNING</> list is identical to that of the output + list of <command>SELECT</>. Only rows that were successfully + inserted or updated will be returned. For example, if a row was + locked but not updated because an <literal>ON CONFLICT DO UPDATE + ... WHERE</literal> clause <replaceable + class="PARAMETER">condition</replaceable> was not satisfied, the + row will not be returned. </para> <para> You must have <literal>INSERT</literal> privilege on a table in - order to insert into it. If a column list is specified, you only - need <literal>INSERT</literal> privilege on the listed columns. + order to insert into it. If <literal>ON CONFLICT DO UPDATE</> is + present the <literal>UPDATE</literal> privilege is also required. + </para> + + <para> + If a column list is specified, you only need + <literal>INSERT</literal> privilege on the listed columns. + Similarly, when <literal>ON CONFLICT DO UPDATE</> is specified, you + only need <literal>UPDATE</> privilege on the column(s) that are + listed to be updated. However, <literal>ON CONFLICT DO UPDATE</> + also requires <literal>SELECT</> privilege on any column whose + values are read in the <literal>ON CONFLICT DO UPDATE</> + expressions or <replaceable>condition</>. + </para> + + <para> Use of the <literal>RETURNING</> clause requires <literal>SELECT</> privilege on all columns mentioned in <literal>RETURNING</>. If you use the <replaceable @@ -115,13 +157,32 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace </varlistentry> <varlistentry> + <term><replaceable class="parameter">alias</replaceable></term> + <listitem> + <para> + A substitute name for the target table. When an alias is provided, it + completely hides the actual name of the table. This is particularly + useful when using <literal>ON CONFLICT DO UPDATE</literal> into a table + named <literal>excluded</literal> as that's also the name of the + pseudo-relation containing the proposed row. + </para> + </listitem> + </varlistentry> + + + <varlistentry> <term><replaceable class="PARAMETER">column_name</replaceable></term> <listitem> <para> The name of a column in the table named by <replaceable class="PARAMETER">table_name</replaceable>. 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.) + composite column leaves the other fields null.) When + referencing a column with <literal>ON CONFLICT DO UPDATE</>, do + not include the table's name in the specification of a target + column. For example, <literal>INSERT ... ON CONFLICT DO UPDATE + tab SET table_name.col = 1</> is invalid (this follows the general + behavior for <command>UPDATE</>). </para> </listitem> </varlistentry> @@ -171,14 +232,35 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace <listitem> <para> An expression to be computed and returned by the <command>INSERT</> - command after each row is inserted. The expression can use any - column names of the table named by <replaceable class="PARAMETER">table_name</replaceable>. + command after each row is inserted (not updated). The + expression can use any column names of the table named by + <replaceable class="PARAMETER">table_name</replaceable>. Write <literal>*</> to return all columns of the inserted row(s). </para> </listitem> </varlistentry> <varlistentry> + <term><literal>conflict_target</literal></term> + <listitem> + <para> + Specify which conflicts <literal>ON CONFLICT</literal> refers to. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>conflict_action</literal></term> + <listitem> + <para> + <literal>DO NOTHING</literal> or <literal>DO UPDATE + SET</literal> clause specifying the action to be performed in + case of a conflict. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><replaceable class="PARAMETER">output_name</replaceable></term> <listitem> <para> @@ -186,9 +268,226 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace </para> </listitem> </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">column_name_index</replaceable></term> + <listitem> + <para> + The name of a <replaceable + class="PARAMETER">table_name</replaceable> column. Part of a + unique index inference clause. Follows <command>CREATE + INDEX</command> format. <literal>SELECT</> privilege on + <replaceable class="PARAMETER">column_name_index</replaceable> + is required. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">expression_index</replaceable></term> + <listitem> + <para> + Similar to <replaceable + class="PARAMETER">column_name_index</replaceable>, but used to + infer expressions on <replaceable + class="PARAMETER">table_name</replaceable> columns appearing + within index definitions (not simple columns). Part of unique + index inference clause. Follows <command>CREATE INDEX</command> + format. <literal>SELECT</> privilege on any column appearing + within <replaceable + class="PARAMETER">expression_index</replaceable> is required. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">collation</replaceable></term> + <listitem> + <para> + When specified, mandates that corresponding <replaceable + class="PARAMETER">column_name_index</replaceable> or + <replaceable class="PARAMETER">expression_index</replaceable> use a + particular collation in order to be matched in the inference clause. + Typically this is omitted, as collations usually do not affect wether or + not a constraint violation occurs. Follows <command>CREATE + INDEX</command> format. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">opclass</replaceable></term> + <listitem> + <para> + When specified, mandates that corresponding <replaceable + class="PARAMETER">column_name_index</replaceable> or + <replaceable class="PARAMETER">expression_index</replaceable> use + particular operator class in order to be matched by the inference + clause. Sometimes this is omitted because the + <emphasis>equality</emphasis> semantics are often equivalent across a + type's operator classes anyway, or because it's sufficient to trust that + the defined unique indexes have the pertinent definition of equality. + Follows <command>CREATE INDEX</command> format. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">index_predicate</replaceable></term> + <listitem> + <para> + Used to allow inference of partial unique indexes. Any indexes + that satisfy the predicate (which need not actually be partial + indexes) can be matched by the rest of the inference clause. + Follows <command>CREATE INDEX</command> format. + <literal>SELECT</> privilege on any column appearing within + <replaceable class="PARAMETER">index_predicate</replaceable> is + required. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">constraint_name</replaceable></term> + <listitem> + <para> + Explicitly specifies an arbiter <emphasis>constraint</emphasis> + by name, rather than inferring a constraint or index. This is + mostly useful for exclusion constraints, that cannot be chosen + in the conventional way (with an inference clause). + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><replaceable class="PARAMETER">condition</replaceable></term> + <listitem> + <para> + An expression that returns a value of type <type>boolean</type>. Only + rows for which this expression returns <literal>true</literal> will be + updated, although all rows will be locked when the + <literal>ON CONFLICT DO UPDATE</> action is taken. + </para> + </listitem> + </varlistentry> </variablelist> </refsect1> + <refsect1 id="sql-on-conflict"> + <title id="sql-on-conflict-title"><literal>ON CONFLICT</literal> Clause</title> + <indexterm zone="SQL-INSERT"> + <primary>UPSERT</primary> + </indexterm> + <indexterm zone="SQL-INSERT"> + <primary>ON CONFLICT</primary> + </indexterm> + <para> + The optional <literal>ON CONFLICT</literal> 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 a constraint + specified by the <parameter>conflict_target</parameter> is + violated, the alternative <parameter>conflict_action</parameter> is + taken. + </para> + + <para> + <parameter>conflict_target</parameter> describes which conflicts + are handled by the <literal>ON CONFLICT</literal> clause. Either a + <emphasis>unique index inference</emphasis> clause or an explicitly + named constraint can be used. For <literal>ON CONFLICT DO + NOTHING</literal>, it is optional to specify a + <parameter>conflict_target</parameter>; when ommitted, conflicts + with all usable constraints (and unique indexes) are handled. For + <literal>ON CONFLICT DO UPDATE</literal>, a conflict target + <emphasis>must</emphasis> be specified. + + Every time an insertion without <literal>ON CONFLICT</literal> + would ordinarily raise an error due to violating one of the + inferred (or explicitly named) constraints, a conflict (as in + <literal>ON CONFLICT</literal>) occurs, and the alternative action, + as specified by <parameter>conflict_action</parameter> is taken. + This happens on a row-by-row basis. + </para> + + <para> + A <emphasis>unique index inference</emphasis> clause consists of + one or more <replaceable + class="PARAMETER">column_name_index</replaceable> columns and/or + <replaceable class="PARAMETER">expression_index</replaceable> + expressions, and a optional <replaceable class="PARAMETER"> + index_predicate</replaceable>. + </para> + + <para> + All the <replaceable class="PARAMETER">table_name</replaceable> + unique indexes that, without regard to order, contain exactly the + specified columns/expressions and, if specified, whose predicate + implies the <replaceable class="PARAMETER"> + index_predicate</replaceable> are chosen as arbiter indexes. Note + that this means an index without a predicate will be used if a + non-partial index matching every other criteria happens to be + available. + </para> + + <para> + If no index matches the inference clause (nor is there a constraint + explicitly named), an error is raised. Deferred constraints are + not supported as arbiters. + </para> + + <para> + <parameter>conflict_action</parameter> defines the action to be + taken in case of conflict. <literal>ON CONFLICT DO + NOTHING</literal> simply avoids inserting a row as its alternative + action. <literal>ON CONFLICT DO UPDATE</literal> updates the + existing row that conflicts with the row proposed for insertion as + its alternative action. + + <literal>ON CONFLICT DO UPDATE</literal> guarantees an atomic + <command>INSERT</command> or <command>UPDATE</command> outcome - provided + there is no independent error, one of those two outcomes is guaranteed, + even under high concurrency. This feature is also known as + <firstterm>UPSERT</firstterm>. + + Note that exclusion constraints are not supported with + <literal>ON CONFLICT DO UPDATE</literal>. + </para> + + <para> + <literal>ON CONFLICT DO UPDATE</literal> optionally accepts + a <literal>WHERE</literal> clause <replaceable>condition</replaceable>. + When provided, the statement only proceeds with updating if + the <replaceable>condition</replaceable> is satisfied. Otherwise, unlike a + conventional <command>UPDATE</command>, the row is still locked for update. + Note that the <replaceable>condition</replaceable> is evaluated last, after + a conflict has been identified as a candidate to update. + </para> + + <para> + The <literal>SET</literal> and <literal>WHERE</literal> clauses in + <literal>ON CONFLICT UPDATE</literal> have access to the existing + row, using the table's name, and to the row + proposed for insertion, using the <varname>excluded</varname> + alias. The <varname>excluded</varname> alias requires + <literal>SELECT</> privilege on any column whose values are read. + + Note that the effects of all per-row <literal>BEFORE INSERT</literal> + triggers are reflected in <varname>excluded</varname> values, since those + effects may have contributed to the row being excluded from insertion. + </para> + + <para> + <command>INSERT</command> with an <literal>ON CONFLICT DO UPDATE</> + clause is a <quote>deterministic</quote> 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 the + conflict-arbitrating unique index. + </para> + </refsect1> + <refsect1> <title>Outputs</title> @@ -198,20 +497,29 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ ( <replace <screen> INSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable> </screen> + However, in the event of an <literal>ON CONFLICT DO UPDATE</> clause + (but <emphasis>not</emphasis> in the event of an <literal>ON + CONFLICT DO NOTHING</> clause), the command tag reports the number of + rows inserted or updated together, of the form +<screen> +UPSERT <replaceable>oid</replaceable> <replaceable class="parameter">count</replaceable> +</screen> The <replaceable class="parameter">count</replaceable> is the number of rows inserted. If <replaceable class="parameter">count</replaceable> is exactly one, and the target table has OIDs, then <replaceable class="parameter">oid</replaceable> is the - <acronym>OID</acronym> assigned to the inserted row. Otherwise - <replaceable class="parameter">oid</replaceable> is zero. + <acronym>OID</acronym> + assigned to the inserted row (but not if there is only a single + updated row). Otherwise <replaceable + class="parameter">oid</replaceable> is zero. </para> <para> If the <command>INSERT</> command contains a <literal>RETURNING</> clause, the result will be similar to that of a <command>SELECT</> statement containing the columns and values defined in the - <literal>RETURNING</> list, computed over the row(s) inserted by the - command. + <literal>RETURNING</> list, computed over the row(s) inserted or + updated by the command. </para> </refsect1> @@ -311,7 +619,65 @@ WITH upd AS ( RETURNING * ) INSERT INTO employees_log SELECT *, current_timestamp FROM upd; -</programlisting></para> +</programlisting> + </para> + <para> + Insert or update new distributors as appropriate. Assumes a unique + index has been defined that constrains values appearing in the + <literal>did</literal> column. Note that an <varname>EXCLUDED</> + expression is used to reference values originally proposed for + insertion: +<programlisting> + INSERT INTO distributors (did, dname) + VALUES (5, 'Gizmo transglobal'), (6, 'Associated Computing, inc') + ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname; +</programlisting> + </para> + <para> + 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 <literal>did</literal> column: +<programlisting> + INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') + ON CONFLICT (did) DO NOTHING; +</programlisting> + </para> + <para> + Insert or update new distributors as appropriate. Example assumes + a unique index has been defined that constrains values appearing in + the <literal>did</literal> column. <literal>WHERE</> clause is + used to limit the rows actually updated (any existing row not + updated will still be locked, though): +<programlisting> + -- Don't update existing distributors based in a certain ZIP code + INSERT INTO distributors AS d (did, dname) VALUES (8, 'Anvil Distribution') + ON CONFLICT (did) DO UPDATE + SET dname = EXCLUDED.dname || ' (formerly ' || d.dname || ')' + WHERE d.zipcode != '21201'; + + -- Name a constraint directly in the statement (uses associated + -- index to arbitrate taking the DO NOTHING action) + INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design') + ON CONFLICT ON CONSTRAINT distributors_pkey DO NOTHING; +</programlisting> + </para> + <para> + Insert new distributor if possible; otherwise + <literal>DO NOTHING</literal>. Example assumes a unique index has been + defined that constrains values appearing in the + <literal>did</literal> column on a subset of rows where the + <literal>is_active</literal> boolean column evaluates to + <literal>true</literal>: +<programlisting> + -- 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, 'Conrad International') + ON CONFLICT (did) WHERE is_active DO NOTHING; +</programlisting> + </para> </refsect1> <refsect1> @@ -321,7 +687,8 @@ INSERT INTO employees_log SELECT *, current_timestamp FROM upd; <command>INSERT</command> conforms to the SQL standard, except that the <literal>RETURNING</> clause is a <productname>PostgreSQL</productname> extension, as is the ability - to use <literal>WITH</> with <command>INSERT</>. + to use <literal>WITH</> with <command>INSERT</>, and the ability to + specify an alternative action with <literal>ON CONFLICT</>. Also, the case in which a column name list is omitted, but not all the columns are filled from the <literal>VALUES</> clause or <replaceable>query</>, |