From edf68b2ed51cb8a1c9fdf7eb13f9b2c883eb5399 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Tue, 10 Nov 2015 00:02:49 +0100 Subject: [PATCH] Improve ON CONFLICT documentation. Author: Peter Geoghegan and Andres Freund Discussion: CAM3SWZScpWzQ-7EJC77vwqzZ1GO8GNmURQ1QqDQ3wRn7AbW1Cg@mail.gmail.com Backpatch: 9.5, where ON CONFLICT was introduced --- doc/src/sgml/ref/insert.sgml | 743 ++++++++++++++++++----------------- 1 file changed, 378 insertions(+), 365 deletions(-) diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 8caf5fe855..945eb69ada 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -99,7 +99,8 @@ INSERT INTO table_name [ AS You must have INSERT privilege on a table in order to insert into it. If ON CONFLICT DO UPDATE is - present the UPDATE privilege is also required. + present, UPDATE privilege on the table is also + required. @@ -126,366 +127,378 @@ INSERT INTO table_name [ AS Parameters - - - with_query - - - The WITH clause allows you to specify one or more - subqueries that can be referenced by name in the INSERT - query. See and - for details. - - - It is possible for the query - (SELECT statement) - to also contain a WITH clause. In such a case both - sets of with_query can be referenced within - the query, but the - second one takes precedence since it is more closely nested. - - - - - - table_name - - - The name (optionally schema-qualified) of an existing table. - - - - - - alias - - - 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 ON CONFLICT DO UPDATE into a table - named excluded as that's also the name of the - pseudo-relation containing the proposed row. - - - - - - - column_name - - - The name of a column in the table named by table_name. - 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's name in the specification of a target - column. For example, INSERT ... ON CONFLICT DO UPDATE - tab SET table_name.col = 1 is invalid (this follows the general - behavior for UPDATE). - - - - - - DEFAULT VALUES - - - All columns will be filled with their default values. - - - - - - expression - - - An expression or value to assign to the corresponding column. - - - - - - DEFAULT - - - The corresponding column will be filled with - its default value. - - - - - - query - - - A query (SELECT statement) that supplies the - rows to be inserted. Refer to the - - statement for a description of the syntax. - - - - - - output_expression - - - An expression to be computed and returned by the INSERT - command after each row is inserted (not updated). The - expression can use any column names of the table named by - table_name. - Write * to return all columns of the inserted row(s). - - - - - - conflict_target - - - Specify which conflicts ON CONFLICT refers to. - - - - - - conflict_action - - - DO NOTHING or DO UPDATE - SET clause specifying the action to be performed in - case of a conflict. - - - - - - output_name - - - A name to use for a returned column. - - - - - - column_name_index - - - The name of a table_name column. Part of a - unique index inference clause. Follows CREATE - INDEX format. SELECT privilege on - column_name_index - is required. - - - - - - expression_index - - - Similar to column_name_index, but used to - infer expressions on table_name columns appearing - within index definitions (not simple columns). Part of unique - index inference clause. Follows CREATE INDEX - format. SELECT privilege on any column appearing - within expression_index is required. - - - - - - collation - - - When specified, mandates that corresponding column_name_index or - expression_index use a - particular collation in order to be matched in the inference clause. - Typically this is omitted, as collations usually do not affect whether or - not a constraint violation occurs. Follows CREATE - INDEX format. - - - - - - opclass - - - When specified, mandates that corresponding column_name_index or - expression_index use - particular operator class in order to be matched by the inference - clause. Sometimes this is omitted because the - equality 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 CREATE INDEX format. - - - - - - index_predicate - - - 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 CREATE INDEX format. - SELECT privilege on any column appearing within - index_predicate is - required. - - - - - - constraint_name - - - Explicitly specifies an arbiter constraint - 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). - - - - - - condition - - - 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. - - - - - - - - <literal>ON CONFLICT</literal> Clause - - UPSERT - - - ON CONFLICT - - - 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 a constraint - specified by the conflict_target is - violated, the alternative conflict_action is - taken. - - - - conflict_target describes which conflicts - are handled by the ON CONFLICT clause. Either a - unique index inference clause or an explicitly - named constraint can be used. For ON CONFLICT DO - NOTHING, it is optional to specify a - conflict_target; when omitted, conflicts - with all usable constraints (and unique indexes) are handled. For - ON CONFLICT DO UPDATE, a conflict target - must be specified. - - Every time an insertion without ON CONFLICT - would ordinarily raise an error due to violating one of the - inferred (or explicitly named) constraints, a conflict (as in - ON CONFLICT) occurs, and the alternative action, - as specified by conflict_action is taken. - This happens on a row-by-row basis. - - - - A unique index inference clause consists of - one or more column_name_index columns and/or - expression_index - expressions, and an optional - index_predicate. - - - - All the table_name - unique indexes that, without regard to order, contain exactly the - specified columns/expressions and, if specified, whose predicate - implies the - index_predicate 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. - - - - 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. - - - - conflict_action defines the action to be - taken in case of conflict. 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. - - ON CONFLICT DO UPDATE guarantees an atomic - INSERT or UPDATE outcome - provided - there is no independent error, one of those two outcomes is guaranteed, - even under high concurrency. This feature is also known as - UPSERT. - - Note that exclusion constraints are not supported with - ON CONFLICT DO UPDATE. - - - - ON CONFLICT DO UPDATE optionally accepts - a WHERE clause condition. - When provided, the statement only proceeds with updating if - the condition is satisfied. Otherwise, unlike a - conventional UPDATE, the row is still locked for update. - Note that the condition is evaluated last, after - a conflict has been identified as a candidate to update. - - - - The SET and WHERE clauses in - ON CONFLICT UPDATE have access to the existing - row, using the table's name, and to the row - proposed for insertion, using the excluded - alias. The excluded alias requires - SELECT privilege on any column whose values are read. - - Note that the effects of all per-row BEFORE INSERT - triggers are reflected in excluded values, since those - effects may have contributed to the row being excluded from insertion. - - - - INSERT with an ON CONFLICT DO UPDATE - clause is a deterministic 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. - + + Inserting + + + This section covers parameters that may be used when only + inserting new rows. Parameters exclusively + used with the ON CONFLICT clause are described + separately. + + + + + with_query + + + The WITH clause allows you to specify one or more + subqueries that can be referenced by name in the INSERT + query. See and + for details. + + + It is possible for the query + (SELECT statement) + to also contain a WITH clause. In such a case both + sets of with_query can be referenced within + the query, but the + second one takes precedence since it is more closely nested. + + + + + + table_name + + + The name (optionally schema-qualified) of an existing table. + + + + + + alias + + + A substitute name for table_name. 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's + also the name of the special table representing rows proposed + for insertion. + + + + + + + column_name + + + The name of a column in the table named by table_name. 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's name in the specification of a target column. For + example, INSERT ... ON CONFLICT DO UPDATE tab SET + table_name.col = 1 is invalid (this follows the general + behavior for UPDATE). + + + + + + DEFAULT VALUES + + + All columns will be filled with their default values. + + + + + + expression + + + An expression or value to assign to the corresponding column. + + + + + + DEFAULT + + + The corresponding column will be filled with + its default value. + + + + + + query + + + A query (SELECT statement) that supplies the + rows to be inserted. Refer to the + + statement for a description of the syntax. + + + + + + output_expression + + + An expression to be computed and returned by the + INSERT command after each row is inserted or + updated. The expression can use any column names of the table + named by table_name. Write + * to return all columns of the inserted or updated + row(s). + + + + + + output_name + + + A name to use for a returned column. + + + + + + + + <literal>ON CONFLICT</literal> Clause + + UPSERT + + + ON CONFLICT + + + 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 + arbiter constraint or index specified by + conflict_target is violated, the + alternative conflict_action 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. + + + + conflict_target can perform + unique index inference. When performing + inference, it consists of one or more column_name_index columns and/or + expression_index + expressions, and an optional + index_predicate. All table_name unique indexes that, + without regard to order, contain exactly the + conflict_target-specified + columns/expressions are inferred (chosen) as arbiter indexes. If + an index_predicate 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. + + + + ON CONFLICT DO UPDATE guarantees an atomic + INSERT or UPDATE outcome; + provided there is no independent error, one of those two outcomes + is guaranteed, even under high concurrency. This is also known as + UPSERT — UPDATE or + INSERT. + + + + + conflict_target + + + Specifies which conflicts ON CONFLICT takes + the alternative action on by choosing arbiter + indexes. Either performs unique index + inference, or names a constraint explicitly. For + ON CONFLICT DO NOTHING, it is optional to + specify a conflict_target; when + omitted, conflicts with all usable constraints (and unique + indexes) are handled. For ON CONFLICT DO + UPDATE, a conflict_target + must be provided. + + + + + + conflict_action + + + conflict_action 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's name (or an alias), and to rows proposed for insertion + using the special excluded table. + SELECT privilege is required on any column in the + target table where corresponding excluded + columns are read. + + + Note that the effects of all per-row BEFORE + INSERT triggers are reflected in + excluded values, since those effects may + have contributed to the row being excluded from insertion. + + + + + + column_name_index + + + The name of a table_name column. Used to + infer arbiter indexes. Follows CREATE + INDEX format. SELECT privilege on + column_name_index + is required. + + + + + + expression_index + + + Similar to column_name_index, but used to + infer expressions on table_name columns appearing + within index definitions (not simple columns). Follows + CREATE INDEX format. SELECT + privilege on any column appearing within expression_index is required. + + + + + + collation + + + When specified, mandates that corresponding column_name_index or + expression_index + 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 CREATE INDEX format. + + + + + + opclass + + + When specified, mandates that corresponding column_name_index or + expression_index + use particular operator class in order to be matched during + inference. Typically this is omitted, as the + equality 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 CREATE + INDEX format. + + + + + + index_predicate + + + 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 CREATE + INDEX format. SELECT privilege on any + column appearing within index_predicate is required. + + + + + + constraint_name + + + Explicitly specifies an arbiter + constraint by name, rather than inferring + a constraint or index. + + + + + + condition + + + 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 + condition is evaluated last, after + a conflict has been identified as a candidate to update. + + + + + + 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. + + + + INSERT with an ON CONFLICT DO UPDATE + clause is a deterministic 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. Note that exclusion constraints are + not supported with ON CONFLICT DO UPDATE. + + + + It is often preferable to use unique index inference rather than + naming a constraint directly using ON CONFLICT ON + CONSTRAINT + constraint_name. 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. + + + + @@ -617,12 +630,12 @@ INSERT INTO employees_log SELECT *, current_timestamp FROM upd; Insert or update new distributors as appropriate. Assumes a unique index has been defined that constrains values appearing in the - did column. Note that an EXCLUDED - expression is used to reference values originally proposed for - insertion: + did column. Note that the special + excluded table is used to reference values originally + proposed for insertion: INSERT INTO distributors (did, dname) - VALUES (5, 'Gizmo transglobal'), (6, 'Associated Computing, inc') + VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc') ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname; -- 2.40.0