From 57cecef6abf7e9f7162696ec3b8847809a85e9b9 Mon Sep 17 00:00:00 2001 From: Peter Geoghegan Date: Fri, 26 Sep 2014 20:59:04 -0700 Subject: [PATCH 8/8] User-visible documentation for INSERT ... ON CONFLICT {UPDATE | IGNORE} INSERT ... ON CONFLICT {UPDATE | IGNORE} is documented as a new clause of the INSERT command. Some potentially surprising interactions with triggers are noted -- BEFORE INSERT per-row triggers must fire without the INSERT path necessarily being taken, for example. All the existing features that INSERT ... ON CONFLICT {UPDATE | IGNORE} interacts with have these interactions noted. This includes postgres_fdw, updatable views, table inheritance, RLS and partial unique indexes. Finally, a user-level description of the new "MVCC violation" that the ON CONFLICT UPDATE variant sometimes requires has been added to "Chapter 13 - Concurrency Control", beside existing commentary on READ COMMITTED mode's special handling of concurrent updates. The new "MVCC violation" introduced seems somewhat distinct from the existing one (i.e. READ COMMITTED's handling of when an UPDATE affects a concurrently updated/deleted tuple, which internally uses a mechanism called EvalPlanQual()), because in READ COMMITTED mode it is no longer necessary for any row version to be conventionally visible to the command's MVCC snapshot for an UPDATE of the row to occur (or for the row to be locked, should the UPDATE's WHERE clause not be satisfied). --- doc/src/sgml/ddl.sgml | 23 +++ doc/src/sgml/fdwhandler.sgml | 8 + doc/src/sgml/keywords.sgml | 7 + doc/src/sgml/mvcc.sgml | 24 +++ doc/src/sgml/plpgsql.sgml | 14 +- doc/src/sgml/postgres-fdw.sgml | 8 + doc/src/sgml/protocol.sgml | 13 +- doc/src/sgml/ref/alter_policy.sgml | 7 +- doc/src/sgml/ref/create_policy.sgml | 50 +++-- doc/src/sgml/ref/create_rule.sgml | 6 +- doc/src/sgml/ref/create_table.sgml | 5 +- doc/src/sgml/ref/create_trigger.sgml | 5 +- doc/src/sgml/ref/create_view.sgml | 33 ++- doc/src/sgml/ref/insert.sgml | 373 ++++++++++++++++++++++++++++++++-- doc/src/sgml/ref/set_constraints.sgml | 6 +- doc/src/sgml/trigger.sgml | 49 ++++- 16 files changed, 573 insertions(+), 58 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 570a003..7b43a10 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -2428,9 +2428,27 @@ VALUES ('Albany', NULL, NULL, 'NY'); + There is limited inheritance support for INSERT + commands with ON CONFLICT clauses. Tables with + children are not generally accepted as targets. One notable + exception is that such tables are accepted as targets for + INSERT commands with ON CONFLICT + IGNORE clauses, provided a unique index inference clause was + omitted (which implies that there is no concern about + which unique index any would-be conflict might arise + from). However, tables that happen to be inheritance children are + accepted as targets for all variants of INSERT + with ON CONFLICT. + + + All check constraints and not-null constraints on a parent table are automatically inherited by its children. Other types of constraints (unique, primary key, and foreign key constraints) are not inherited. + Therefore, INSERT with ON CONFLICT + unique index inference considers only unique constraints/indexes + directly associated with the child + table. @@ -2515,6 +2533,11 @@ VALUES ('Albany', NULL, NULL, 'NY'); not INSERT or ALTER TABLE ... RENAME) typically default to including child tables and support the ONLY notation to exclude them. + INSERT with an ON CONFLICT + UPDATE clause does not support the + ONLY notation, and so in effect tables with + inheritance children are not supported for the ON + CONFLICT variant. Commands that do database maintenance and tuning (e.g., REINDEX, VACUUM) typically only work on individual, physical tables and do not diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index c1daa4b..0c3dcb5 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -1014,6 +1014,14 @@ GetForeignServerByName(const char *name, bool missing_ok); source provides. + + INSERT with an ON CONFLICT clause is not supported + with a unique index inference specification (this implies that ON + CONFLICT UPDATE is never supported, since the specification is + mandatory there). When planning an INSERT, + PlanForeignModify should reject these cases. + + diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml index b0dfd5f..ea58211 100644 --- a/doc/src/sgml/keywords.sgml +++ b/doc/src/sgml/keywords.sgml @@ -854,6 +854,13 @@ + CONFLICT + non-reserved + + + + + CONNECT reserved diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index a0d6867..5e310d7 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -326,6 +326,30 @@ + INSERT with an ON CONFLICT UPDATE clause is + another special case. In Read Committed mode, the implementation will + either insert or update each row proposed for insertion, with either one of + those two outcomes guaranteed. This is a useful guarantee for many + use-cases, but it implies that further liberties must be taken with + snapshot isolation. Should a conflict originate in another transaction + whose effects are not visible to the INSERT, the + UPDATE may affect that row, even though it may be the + case that no version of that row is conventionally visible to + the command. In the same vein, if the secondary search condition of the + command (an explicit WHERE clause) is supplied, it is only + evaluated on the most recent row version, which is not necessarily the + version conventionally visible to the command (if indeed there is a row + version conventionally visible to the command at all). + + + + INSERT with an ON CONFLICT IGNORE clause may + have insertion not proceed for a row due to the outcome of another + transaction whose effects are not visible to the INSERT + snapshot. Again, this is only the case in Read Committed mode. + + + Because of the above rule, it is possible for an updating command to see an inconsistent snapshot: it can see the effects of concurrent updating commands on the same rows it is trying to update, but it diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index 69a0885..59a5945 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -2607,7 +2607,11 @@ END; This example uses exception handling to perform either - UPDATE or INSERT, as appropriate: + UPDATE or INSERT, as appropriate. It is + recommended that applications use INSERT with + ON CONFLICT UPDATE rather than actually emulating this + pattern. This example serves only to illustrate use of + PL/pgSQL control flow structures: CREATE TABLE db (a INT PRIMARY KEY, b TEXT); @@ -3771,9 +3775,11 @@ RAISE unique_violation USING MESSAGE = 'Duplicate user ID: ' || user_id; INSERT and UPDATE operations, the return value should be NEW, which the trigger function may modify to support INSERT RETURNING and UPDATE RETURNING - (this will also affect the row value passed to any subsequent triggers). - For DELETE operations, the return value should be - OLD. + (this will also affect the row value passed to any subsequent triggers, + or passed to a special EXCLUDED alias reference within + an INSERT statement with an ON CONFLICT UPDATE + clause). For DELETE operations, the return + value should be OLD. diff --git a/doc/src/sgml/postgres-fdw.sgml b/doc/src/sgml/postgres-fdw.sgml index 43adb61..fa39661 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -69,6 +69,14 @@ + Note that postgres_fdw currently lacks support for + INSERT statements with an ON CONFLICT + UPDATE clause. However, the ON CONFLICT IGNORE + clause is supported, provided a unique index inference specification + is omitted. + + + It is generally recommended that the columns of a foreign table be declared with exactly the same data types, and collations if applicable, as the referenced columns of the remote table. Although postgres_fdw diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index efe75ea..a198182 100644 --- a/doc/src/sgml/protocol.sgml +++ b/doc/src/sgml/protocol.sgml @@ -2998,9 +2998,16 @@ CommandComplete (B) INSERT oid rows, where rows is the number of rows - inserted. oid is the object ID - of the inserted row if rows is 1 - and the target table has OIDs; + inserted. However, if and only if ON CONFLICT + UPDATE is specified, then the tag is UPSERT + oid + rows, where + rows is the number of rows inserted + or updated. + oid is the object ID of the + inserted row if rows is 1 and the + target table has OIDs, and (for the UPSERT + tag), the row was actually inserted rather than updated; otherwise oid is 0. diff --git a/doc/src/sgml/ref/alter_policy.sgml b/doc/src/sgml/ref/alter_policy.sgml index 796035e..86bda92 100644 --- a/doc/src/sgml/ref/alter_policy.sgml +++ b/doc/src/sgml/ref/alter_policy.sgml @@ -93,8 +93,11 @@ ALTER POLICY name ON documentation for + INSERT with ON CONFLICT UPDATE). + The USING expression applies to records which are being retrieved from the + table. diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 8ef8556..fcfcb02 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -63,7 +63,8 @@ CREATE POLICY name ON INSERT with ON CONFLICT UPDATE). @@ -237,6 +238,19 @@ CREATE POLICY name ON + + Note that INSERT with ON CONFLICT + UPDATE requires that an INSERT policy WITH + CHECK expression also passes for both any existing tuple in the target + table that necessitates that the UPDATE path be + taken, and the final tuple added back into the relation. + INSERT policies are separately combined using + OR, and this distinct set of policy expressions must + always pass, regardless of whether any or all UPDATE + policies also pass (in the same tuple check). However, successfully + inserted tuples are not subject to UPDATE policy + enforcement. + @@ -245,18 +259,28 @@ CREATE POLICY name ON Using UPDATE for a policy means that it will apply - to UPDATE commands. As UPDATE - involves pulling an existing record and then making changes to some - portion (but possibly not all) of the record, the - UPDATE policy accepts both a USING expression and - a WITH CHECK expression. The USING expression will be used to - determine which records the UPDATE command will - see to operate against, while the WITH CHECK - expression defines what rows are allowed to be added back into the - relation (similar to the INSERT policy). - Any rows whose resulting values do not pass the - WITH CHECK expression will cause an ERROR and the - entire command will be aborted. + to UPDATE commands (or auxiliary ON + CONFLICT UPDATE clauses of INSERT + commands). As UPDATE involves pulling an existing + record and then making changes to some portion (but possibly not all) + of the record, the UPDATE policy accepts both a + USING expression and a WITH CHECK expression. The USING expression + will be used to determine which records the UPDATE + command will see to operate against, while the WITH + CHECK expression defines what rows are allowed to be added + back into the relation (similar to the INSERT + policy). Any rows whose resulting values do not pass the WITH + CHECK expression will cause an ERROR and the entire command + will be aborted. + + + Note that INSERT with ON CONFLICT + UPDATE requires that an UPDATE policy + USING expression always be treated as a WITH CHECK + expression. This UPDATE policy must + always pass, regardless of whether any + INSERT policy also passes in the same + tuple check. diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index 677766a..9b5c740 100644 --- a/doc/src/sgml/ref/create_rule.sgml +++ b/doc/src/sgml/ref/create_rule.sgml @@ -136,7 +136,11 @@ CREATE [ OR REPLACE ] RULE name AS The event is one of SELECT, INSERT, UPDATE, or - DELETE. + DELETE. Note that an + INSERT containing an ON + CONFLICT clause is unsupported. Consider using an + updatable view instead, which have limited support for + ON CONFLICT IGNORE only. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 299cce8..a9c1124 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -708,7 +708,10 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not - deferrable. + deferrable. Note that constraints that were created with this + clause cannot be used as arbiters of whether or not to take the + alternative path with an INSERT statement + that includes an ON CONFLICT UPDATE clause. diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index 29b815c..26a0986 100644 --- a/doc/src/sgml/ref/create_trigger.sgml +++ b/doc/src/sgml/ref/create_trigger.sgml @@ -76,7 +76,10 @@ CREATE [ CONSTRAINT ] TRIGGER name executes once for any given operation, regardless of how many rows it modifies (in particular, an operation that modifies zero rows will still result in the execution of any applicable FOR - EACH STATEMENT triggers). + EACH STATEMENT triggers). Note that since + INSERT with an ON CONFLICT UPDATE + clause is considered an INSERT statement, no + UPDATE statement level trigger will be fired. diff --git a/doc/src/sgml/ref/create_view.sgml b/doc/src/sgml/ref/create_view.sgml index 5dadab1..599c1cb 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -286,8 +286,9 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; Simple views are automatically updatable: the system will allow INSERT, UPDATE and DELETE statements - to be used on the view in the same way as on a regular table. A view is - automatically updatable if it satisfies all of the following conditions: + to be used on the view in the same way as on a regular table (aside from + the limitations on ON CONFLICT noted below). A view is automatically + updatable if it satisfies all of the following conditions: @@ -383,6 +384,34 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; not need any permissions on the underlying base relations (see ). + + INSERT with an ON CONFLICT clause + is only supported on updatable views under specific circumstances. + If a set of columns/expressions has been provided with which to + infer a unique index to consider as the arbiter of whether the + statement ultimately takes an alternative path - if a would-be + duplicate violation in some particular unique index is tacitly + taken as provoking an alternative UPDATE or + IGNORE path - then updatable views are not supported. + Since this specification is already mandatory for + INSERT with ON CONFLICT UPDATE, + this implies that only the ON CONFLICT IGNORE variant + is supported, and only when there is no such specification. For + example: + + + +-- Unsupported: +INSERT INTO my_updatable_view(key, val) VALUES(1, 'foo') ON CONFLICT (key) + UPDATE SET val = EXCLUDED.val; +INSERT INTO my_updatable_view(key, val) VALUES(1, 'bar') ON CONFLICT (key) + IGNORE; + +-- Supported (note the omission of "key" column): +INSERT INTO my_updatable_view(key, val) VALUES(1, 'baz') ON CONFLICT + IGNORE; + + diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index a3cccb9..40b7566 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -24,6 +24,14 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ ( column_name [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } + [ ON CONFLICT [ ( { column_name_index | ( expression_index ) } [, ...] [ WHERE index_condition ] ) ] + { IGNORE | UPDATE + SET { column_name = { expression | DEFAULT } | + ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) + } [, ...] + [ WHERE condition ] + } + ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] @@ -32,9 +40,15 @@ INSERT INTO table_name [ ( Description - INSERT 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. + INSERT 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. An alternative path + (IGNORE or UPDATE) can + optionally be specified, to be taken in the event of detecting that + proceeding with insertion would result in a conflict (i.e. a + conflicting tuple already exists). The alternative path is + considered individually for each row proposed for insertion, and is + taken (or not taken) once per row. @@ -59,25 +73,214 @@ INSERT INTO table_name [ ( + The optional ON CONFLICT clause specifies a path to + take as an alternative to raising a conflict related error. + ON CONFLICT IGNORE simply avoids inserting any + individual row when it is determined that a conflict related error + would otherwise need to be raised. ON CONFLICT UPDATE + has the system take an UPDATE path in respect of + such rows instead. ON CONFLICT UPDATE guarantees an + atomic INSERT or UPDATE + outcome - provided there is no incidental error, one of those two + outcomes is guaranteed, even under high concurrency. + + + + ON CONFLICT 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. + + + + ON CONFLICT UPDATE is effectively an auxiliary query of + its parent INSERT. Two aliases are visible to + the auxiliary query only - TARGET and + EXCLUDED. The first alias is just a standard alias for + the target relation in the context of the auxiliary query, while + the second alias refers to rows originally proposed for insertion. + Both aliases can be used in the auxiliary query targetlist and + WHERE clause. This allows expressions (in particular, + assignments) to reference rows originally proposed for insertion. + Note that the effects of all per-row BEFORE INSERT + triggers are carried forward. This is particularly useful for + multi-insert ON CONFLICT UPDATE statements; when + inserting or updating multiple rows, constants or parameter values + need only appear once. + + + + There are several restrictions on the ON CONFLICT + UPDATE clause that do not apply to UPDATE + statements. Subqueries may not appear in either the + UPDATE targetlist, nor its WHERE + clause (although simple multi-assignment expressions are + supported). WHERE CURRENT OF cannot be used. In + general, only columns in the target table, and excluded values + originally proposed for insertion may be referenced. Operators and + functions may be used freely, though. + + + + INSERT with an ON CONFLICT 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. Note that the ordinary rules + for unique indexes with regard to null apply analogously to whether + or not an arbitrating unique index indicates if the alternative + path should be taken. This means that when a null value appears in + any uniquely constrained tuple's attribute in an + INSERT statement with ON CONFLICT + UPDATE, rows proposed for insertion will never take the + alternative path (provided that a BEFORE ROW + INSERT trigger does not make null values non-null before + insertion); the statement will always insert, assuming there is no + unrelated error. Note that merely locking a row (by having it not + satisfy the WHERE clause condition) + does not count towards whether or not the row has been affected + multiple times (and whether or not a cardinality violation error is + raised). However, the implementation checks for cardinality + violations after locking the row, and before updating (or + considering updating), so a cardinality violation may be raised + despite the fact that the row would not otherwise have gone on to + be updated if and only if the existing row was updated by the + ON CONFLICT UPDATE command at least once + already. + + + + ON CONFLICT UPDATE requires a unique index + inference specification, which consists of one or more + column_name_index + columns and/or expression_index expressions on + columns, appearing between parenthesis. These are used to infer a + single unique index to limit pre-checking for conflicts to (if no + appropriate index is available, an error is raised). A subset of + the table to limit the check for conflicts to can optionally also + be specified using index_condition. Note that any + available unique index must only cover at least that subset in + order to be arbitrate taking the alternative path; it need not + match exactly, and so a non-partial unique index that otherwise + matches is applicable. ON CONFLICT IGNORE makes an + inference specification optional; omitting the specification + indicates a total indifference to where any conflict could occur, + which isn't always appropriate. At times, it may be desirable for + ON CONFLICT IGNORE to not suppress + a conflict related error associated with an index where that isn't + explicitly anticipated. Note that ON CONFLICT UPDATE + assignment may result in a uniqueness violation, just as with a + conventional UPDATE. + + + + Columns and/or expressions appearing in a unique index inference + specification must match all the columns/expressions of some + existing unique index on table_name - there can be no + columns/expressions from the unique index that do not appear in the + inference specification, nor can there be any columns/expressions + appearing in the inference specification that do not appear in the + unique index definition. However, the order of the + columns/expressions in the index definition, or whether or not the + index definition specified NULLS FIRST or + NULLS LAST, or the internal sort order of each column + (whether DESC or ASC were specified) are + all irrelevant. Deferred unique constraints are not supported as + arbiters of whether an alternative ON CONFLICT path + should be taken. + + + + The definition of a conflict for the purposes of ON + CONFLICT is somewhat subtle, although the exact definition is + seldom of great interest. A conflict is either a unique violation + from a unique constraint (or unique index), or an exclusion + violation from an exclusion constraint. Only unique indexes can be + inferred with a unique index inference specification, which is + required for the UPDATE variant, so in effect + only unique constraints (and unique indexes) are supported by the + UPDATE variant. In contrast to the rules around + certain other SQL clauses, like the DISTINCT + clause, the definition of a duplicate (a conflict) is based on + whatever unique indexes happen to be defined on columns on the + table. This means that if a user-defined type has multiple sort + orders, and the "equals" operator of any of those available sort + orders happens to be inconsistent (which goes against an unenforced + convention of PostgreSQL), the exact + behavior depends on the choice of operator class when the unique + index was created initially, and not any other consideration such + as the default operator class for the type of each indexed column. + If there are multiple unique indexes available that seem like + equally suitable candidates, but with inconsistent definitions of + "equals", then the system chooses whatever it estimates to be the + cheapest one to use as an arbiter of taking the alternative + UPDATE/IGNORE path. + + + + The optional index_condition can be used to + allow the inference specification to infer that a partial unique + index can be used. Any unique index that otherwise satisfies the + inference specification, while also covering at least all the rows + in the table covered by index_condition may be used. It is + recommended that the partial index predicate of the unique index + intended to be used as the arbiter of taking the alternative path + be matched exactly, but this is not required. Note that an error + will be raised if an arbiter unique index is chosen that does not + cover the tuple or tuples ultimately proposed for insertion. + However, an overly specific index_condition does not imply that + arbitrating conflicts will be limited to the subset of rows covered + by the inferred unique index corresponding to index_condition. + + + The optional RETURNING clause causes INSERT - to compute and return value(s) based on each row actually inserted. + to compute and return value(s) based on each row actually inserted + (or updated, if an ON CONFLICT 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 RETURNING list is identical to that of the output list - of SELECT. + of SELECT. Only rows that were successfully inserted + or updated will be returned. If a row was locked but not updated + because an ON CONFLICT UPDATE WHERE clause + did not pass, the row will not be returned. Since + RETURNING is not part of the UPDATE + auxiliary query, the special ON CONFLICT UPDATE aliases + (TARGET and EXCLUDED) may not be + referenced; only the row as it exists after updating (or + inserting) is returned. You must have INSERT privilege on a table in - order to insert into it. If a column list is specified, you only - need INSERT privilege on the listed columns. - Use of the RETURNING clause requires SELECT - privilege on all columns mentioned in RETURNING. - If you use the query clause to insert rows from a - query, you of course need to have SELECT privilege on - any table or column used in the query. + order to insert into it, as well as UPDATE + privilege if and only if ON CONFLICT UPDATE + is specified. If a column list is specified, you only need + INSERT privilege on the listed columns. + Similarly, when ON CONFLICT UPDATE is specified, you + only need UPDATE privilege on the column(s) that are + listed to be updated, as well as SELECT privilege on any column + whose values are read in the ON CONFLICT UPDATE + expressions or condition. Use of the + RETURNING clause requires SELECT privilege + on all columns mentioned in RETURNING. If you use the + query clause to insert + rows from a query, you of course need to have + SELECT privilege on any table or column used in + the query. @@ -121,7 +324,54 @@ INSERT INTO table_name [ ( 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.) + composite column leaves the other fields null.) When + referencing a column with ON CONFLICT UPDATE, do not + include the table's name in the specification of a target + column. For example, INSERT ... ON CONFLICT UPDATE tab + SET TARGET.col = 1 is invalid. + + + + + + column_name_index + + + The name of a table_name column (with several + columns potentially named). These are used to infer a + particular unique index defined on table_name. This requires + ON CONFLICT UPDATE and ON CONFLICT + IGNORE to assume that all expected sources of uniqueness + violations originate within the columns/rows constrained by the + unique index. When this is omitted, (which is forbidden with + the ON CONFLICT UPDATE variant), the system checks + for sources of uniqueness violations ahead of time in all unique + indexes. Otherwise, only a single specified unique index is + checked ahead of time, and uniqueness violation errors can + appear for conflicts originating in any other unique index. If + a unique index cannot be inferred, an error is raised. + + + + + + expression_index + + + Equivalent to column_name_index, but used to + infer a particular expressional index instead. + + + + + + index_condition + + + Used to allow inference of partial unique indexes. @@ -167,12 +417,25 @@ INSERT INTO table_name [ ( + 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 UPDATE path is taken. + + + + + output_expression An expression to be computed and returned by the INSERT - command after each row is inserted. The expression can use any - column names of the table named by table_name. + 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). @@ -198,20 +461,29 @@ INSERT INTO table_name [ ( INSERT oid count + However, in the event of an ON CONFLICT UPDATE clause + (but not in the event of an ON + CONFLICT IGNORE clause), the command tag reports the number of + rows inserted or updated together, of the form + +UPSERT oid count + The count is the number of rows inserted. If count is exactly one, and the target table has OIDs, then oid is the - OID assigned to the inserted row. Otherwise - oid is zero. + OID + assigned to the inserted row (but not if there is only a single + updated row). Otherwise oid is zero.. If the INSERT command contains a RETURNING clause, the result will be similar to that of a SELECT statement containing the columns and values defined in the - RETURNING list, computed over the row(s) inserted by the - command. + RETURNING list, computed over the row(s) inserted or + updated by the command. @@ -311,7 +583,63 @@ WITH upd AS ( RETURNING * ) 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: + + INSERT INTO distributors (did, dname) + VALUES (5, 'Gizmo transglobal'), (6, 'Associated Computing, inc') + ON CONFLICT (did) UPDATE SET dname = EXCLUDED.dname + + + + 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 (although + since the IGNORE variant was used, the specification of + columns to infer a unique index from is not mandatory): + + INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') + ON CONFLICT (did) IGNORE + + + + 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): + + -- Don't update existing distributors based in a certain ZIP code + INSERT INTO distributors (did, dname) VALUES (8, 'Anvil Distribution') + ON CONFLICT (did) UPDATE + SET dname = EXCLUDED.dname || ' (formerly ' || TARGET.dname || ')' + WHERE TARGET.zipcode != '21201' + + + + Insert new distributor if possible; otherwise + IGNORE. 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: + + -- 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 if that was + -- all that was available. + INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design') + ON CONFLICT (did WHERE is_active) IGNORE + + @@ -321,7 +649,8 @@ INSERT INTO employees_log SELECT *, current_timestamp FROM upd; INSERT conforms to the SQL standard, except that the RETURNING clause is a PostgreSQL extension, as is the ability - to use WITH with INSERT. + to use WITH with INSERT, and the ability to + specify an alternative path 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 query, diff --git a/doc/src/sgml/ref/set_constraints.sgml b/doc/src/sgml/ref/set_constraints.sgml index 7c31871..1e0a2f8 100644 --- a/doc/src/sgml/ref/set_constraints.sgml +++ b/doc/src/sgml/ref/set_constraints.sgml @@ -69,7 +69,11 @@ SET CONSTRAINTS { ALL | name [, ... Currently, only UNIQUE, PRIMARY KEY, REFERENCES (foreign key), and EXCLUDE - constraints are affected by this setting. + constraints are affected by this setting. Note that constraints + that were created with this clause cannot be used as arbiters of + whether or not to take the alternative path with an + INSERT statement that includes an ON + CONFLICT UPDATE clause. NOT NULL and CHECK constraints are always checked immediately when a row is inserted or modified (not at the end of the statement). diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index f94aea1..5141690 100644 --- a/doc/src/sgml/trigger.sgml +++ b/doc/src/sgml/trigger.sgml @@ -40,14 +40,17 @@ On tables and foreign tables, triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, - or once per SQL statement. - UPDATE triggers can moreover be set to fire only if - certain columns are mentioned in the SET clause of the - UPDATE statement. - Triggers can also fire for TRUNCATE statements. - If a trigger event occurs, the trigger's function is called at the - appropriate time to handle the event. Foreign tables do not support the - TRUNCATE statement at all. + or once per SQL statement. If an + INSERT contains an ON CONFLICT UPDATE + clause, it is possible that the effects of a BEFORE insert trigger and + a BEFORE update trigger can both be applied twice, if a reference to + an EXCLUDED column appears. UPDATE + triggers can moreover be set to fire only if certain columns are + mentioned in the SET clause of the + UPDATE statement. Triggers can also fire for + TRUNCATE statements. If a trigger event occurs, + the trigger's function is called at the appropriate time to handle the + event. Foreign tables do not support the TRUNCATE statement at all. @@ -119,6 +122,36 @@ + If an INSERT contains an ON CONFLICT + UPDATE clause, it is possible that the effects of all row-level + BEFORE INSERT triggers and all + row-level BEFORE UPDATE triggers can both be + applied in a way that is apparent from the final state of the updated + row, if an EXCLUDED column is referenced. There need not + be an EXCLUDED column reference for both sets of BEFORE + row-level triggers to execute, though. The possibility of surprising + outcomes should be considered when there are both BEFORE + INSERT and BEFORE + UPDATE row-level triggers that both affect a row + being inserted/updated (this can still be problematic if the + modifications are more or less equivalent if they're not also + idempotent). Note that statement-level UPDATE + triggers are executed when ON CONFLICT UPDATE is + specified, regardless of whether or not any rows were affected by + the UPDATE. An INSERT with + an ON CONFLICT UPDATE clause will execute + statement-level BEFORE INSERT + triggers first, then statement-level BEFORE + UPDATE triggers, followed by statement-level + AFTER UPDATE triggers and finally + statement-level AFTER INSERT + triggers. ON CONFLICT UPDATE is not supported on + views (Only ON CONFLICT IGNORE is supported on + updatable views); therefore, unpredictable interactions with + INSTEAD OF triggers are not possible. + + + Trigger functions invoked by per-statement triggers should always return NULL. Trigger functions invoked by per-row triggers can return a table row (a value of -- 1.9.1