From 168d5805e4c08bed7b95d351bf097cff7c07dd65 Mon Sep 17 00:00:00 2001 From: Andres Freund Date: Fri, 8 May 2015 05:31:36 +0200 Subject: Add support for INSERT ... ON CONFLICT DO NOTHING/UPDATE. The newly added ON CONFLICT clause allows to specify an alternative to raising a unique or exclusion constraint violation error when inserting. ON CONFLICT refers to constraints that can either be specified using a inference clause (by specifying the columns of a unique constraint) or by naming a unique or exclusion constraint. DO NOTHING avoids the constraint violation, without touching the pre-existing row. DO UPDATE SET ... [WHERE ...] updates the pre-existing tuple, and has access to both the tuple proposed for insertion and the existing tuple; the optional WHERE clause can be used to prevent an update from being executed. The UPDATE SET and WHERE clauses have access to the tuple proposed for insertion using the "magic" EXCLUDED alias, and to the pre-existing tuple using the table name or its alias. This feature is often referred to as upsert. This is implemented using a new infrastructure called "speculative insertion". It is an optimistic variant of regular insertion that first does a pre-check for existing tuples and then attempts an insert. If a violating tuple was inserted concurrently, the speculatively inserted tuple is deleted and a new attempt is made. If the pre-check finds a matching tuple the alternative DO NOTHING or DO UPDATE action is taken. If the insertion succeeds without detecting a conflict, the tuple is deemed inserted. To handle the possible ambiguity between the excluded alias and a table named excluded, and for convenience with long relation names, INSERT INTO now can alias its target table. Bumps catversion as stored rules change. Author: Peter Geoghegan, with significant contributions from Heikki Linnakangas and Andres Freund. Testing infrastructure by Jeff Janes. Reviewed-By: Heikki Linnakangas, Andres Freund, Robert Haas, Simon Riggs, Dean Rasheed, Stephen Frost and many others. --- doc/src/sgml/fdwhandler.sgml | 7 + doc/src/sgml/keywords.sgml | 7 + doc/src/sgml/mvcc.sgml | 23 +- doc/src/sgml/plpgsql.sgml | 14 +- doc/src/sgml/postgres-fdw.sgml | 8 + doc/src/sgml/protocol.sgml | 13 +- doc/src/sgml/ref/create_policy.sgml | 63 ++++-- doc/src/sgml/ref/create_rule.sgml | 6 +- doc/src/sgml/ref/create_table.sgml | 4 +- doc/src/sgml/ref/create_trigger.sgml | 5 +- doc/src/sgml/ref/create_view.sgml | 9 +- doc/src/sgml/ref/insert.sgml | 403 +++++++++++++++++++++++++++++++++-- doc/src/sgml/trigger.sgml | 48 ++++- 13 files changed, 550 insertions(+), 60 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/fdwhandler.sgml b/doc/src/sgml/fdwhandler.sgml index 04f3c224331..bc06d2cbb26 100644 --- a/doc/src/sgml/fdwhandler.sgml +++ b/doc/src/sgml/fdwhandler.sgml @@ -1050,6 +1050,13 @@ GetForeignServerByName(const char *name, bool missing_ok); source provides. + + INSERT with an ON CONFLICT clause does not + support specifying the conflict target, as remote constraints are not + locally known. This in turn implies that ON CONFLICT DO + UPDATE is not supported, since the specification is mandatory there. + + diff --git a/doc/src/sgml/keywords.sgml b/doc/src/sgml/keywords.sgml index b0dfd5ff75b..ea582116ab9 100644 --- a/doc/src/sgml/keywords.sgml +++ b/doc/src/sgml/keywords.sgml @@ -853,6 +853,13 @@ + + CONFLICT + non-reserved + + + + CONNECT diff --git a/doc/src/sgml/mvcc.sgml b/doc/src/sgml/mvcc.sgml index f88b16e778a..313198800cb 100644 --- a/doc/src/sgml/mvcc.sgml +++ b/doc/src/sgml/mvcc.sgml @@ -326,8 +326,27 @@ - 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 + INSERT with an ON CONFLICT DO UPDATE clause + behaves similarly. In Read Committed mode, each row proposed for insertion + will either insert or update. Unless there are unrelated errors, one of + those two outcomes is guaranteed. If a conflict originates in another + transaction whose effects are not yet visible to the INSERT + , the UPDATE clause will affect that row, + even though possibly no version of that row is + conventionally visible to the command. + + + + INSERT with an ON CONFLICT DO + NOTHING 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 rules, 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 does not see effects of those commands on other rows in the database. This behavior makes Read Committed mode unsuitable for commands that diff --git a/doc/src/sgml/plpgsql.sgml b/doc/src/sgml/plpgsql.sgml index d36acf6d996..9a7763d18c9 100644 --- a/doc/src/sgml/plpgsql.sgml +++ b/doc/src/sgml/plpgsql.sgml @@ -2623,7 +2623,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 DO UPDATE rather than actually using + this pattern. This example serves primarily to illustrate use of + PL/pgSQL control flow structures: CREATE TABLE db (a INT PRIMARY KEY, b TEXT); @@ -3852,9 +3856,11 @@ ASSERT condition , 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 DO + 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 43adb61455d..1079140de28 100644 --- a/doc/src/sgml/postgres-fdw.sgml +++ b/doc/src/sgml/postgres-fdw.sgml @@ -68,6 +68,14 @@ in your user mapping must have privileges to do these things.) + + Note that postgres_fdw currently lacks support for + INSERT statements with an ON CONFLICT DO + UPDATE clause. However, the ON CONFLICT DO NOTHING + 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 diff --git a/doc/src/sgml/protocol.sgml b/doc/src/sgml/protocol.sgml index 3a753a0b9b1..ac13d3201ce 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/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml index 457911e0c30..e826984633c 100644 --- a/doc/src/sgml/ref/create_policy.sgml +++ b/doc/src/sgml/ref/create_policy.sgml @@ -78,11 +78,13 @@ CREATE POLICY name ON ON CONFLICT DO + UPDATE and INSERT policies are not combined in this way, but + rather enforced as noted at each stage of ON CONFLICT execution). + Further, for commands which can have both USING and WITH CHECK policies (ALL + and UPDATE), if no WITH CHECK policy is defined then the USING policy will be + used for both what rows are visible (normal USING case) and which rows will + be allowed to be added (WITH CHECK case). @@ -263,6 +265,12 @@ CREATE POLICY name ON + + Note that INSERT with ON CONFLICT DO + UPDATE requires that any INSERT policy + WITH CHECK expression passes for any rows appended to the relation by + the INSERT path only. + @@ -271,22 +279,39 @@ 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. Note that if only a - USING clause is specified then that clause will be - used for both USING and + to UPDATE commands (or auxiliary ON + CONFLICT DO 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 if only a USING clause is specified then + that clause will be used for both USING and WITH CHECK cases. + + Note, however, that INSERT with ON CONFLICT + DO UPDATE requires that an UPDATE policy + USING expression always be enforced as a + WITH CHECK expression. This + UPDATE policy must always pass when the + UPDATE path is taken. Any existing row that + necessitates that the UPDATE path be taken must pass + the (UPDATE or ALL) USING qualifications (combined + using OR), which are always enforced as WTIH CHECK + options in this context (the UPDATE path will + never be silently avoided; an error will be thrown + instead). Finally, the final row appended to the relation must pass + any WITH CHECK options that a conventional + UPDATE is required to pass. + diff --git a/doc/src/sgml/ref/create_rule.sgml b/doc/src/sgml/ref/create_rule.sgml index 677766a2d5e..53fdf566214 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 cannot be used on tables that have + either INSERT or UPDATE + rules. Consider using an updatable view instead. diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index be7ebd5f54f..fac7e1ec5e1 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -717,7 +717,9 @@ 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 deferrable constraints cannot be used as + conflict arbitrators in an INSERT statement that + includes an ON CONFLICT DO UPDATE clause. diff --git a/doc/src/sgml/ref/create_trigger.sgml b/doc/src/sgml/ref/create_trigger.sgml index aae0b41cd2e..4bde8150122 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 with an + INSERT with an ON CONFLICT DO UPDATE + clause, both INSERT and + 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 5dadab1dee9..8fa3564021e 100644 --- a/doc/src/sgml/ref/create_view.sgml +++ b/doc/src/sgml/ref/create_view.sgml @@ -333,7 +333,8 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; If the view is automatically updatable the system will convert any INSERT, UPDATE or DELETE statement on the view into the corresponding statement on the underlying base - relation. + relation. INSERT statements that have an ON + CONFLICT UPDATE clause are fully supported. @@ -345,8 +346,10 @@ CREATE VIEW vista AS SELECT text 'Hello World' AS hello; condition, and thus is no longer visible through the view. Similarly, an INSERT command can potentially insert base-relation rows that do not satisfy the WHERE condition and thus are not - visible through the view. The CHECK OPTION may be used to - prevent INSERT and UPDATE commands from creating + visible through the view (ON CONFLICT UPDATE may + similarly affect an existing row not visible through the view). + The CHECK OPTION may be used to prevent + INSERT and UPDATE commands from creating such rows that are not visible through the view. diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index a3cccb9f7c7..c88d1b7b50a 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -22,9 +22,24 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] -INSERT INTO table_name [ ( column_name [, ...] ) ] +INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } + [ ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] + +where conflict_target can be one of: + + ( { column_name_index | ( expression_index ) } [ COLLATE collation ] [ opclass ] [, ...] ) [ WHERE index_predicate ] + ON CONSTRAINT constraint_name + +and conflict_action is one of: + + DO NOTHING + DO UPDATE SET { column_name = { expression | DEFAULT } | + ( column_name [, ...] ) = ( { expression | DEFAULT } [, ...] ) | + ( column_name [, ...] ) = ( sub-SELECT ) + } [, ...] + [ WHERE condition ] @@ -58,20 +73,47 @@ INSERT INTO table_name [ ( + + ON CONFLICT can be used to specify an alternative + action to raising a unique constraint or exclusion constraint + violation error . (See below.) + + The optional RETURNING clause causes 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 - RETURNING list is identical to that of the output list - of SELECT. + 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's columns is allowed. The syntax of + the RETURNING list is identical to that of the output + list of SELECT. 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 condition was not satisfied, the + row will not be 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. + order to insert into it. If ON CONFLICT DO UPDATE is + present the UPDATE privilege is also required. + + + + 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 condition. + + + Use of the RETURNING clause requires SELECT privilege on all columns mentioned in RETURNING. If you use the table_name [ ( + + 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 @@ -121,7 +177,12 @@ 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 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). @@ -171,13 +232,34 @@ INSERT INTO table_name [ ( 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). + + 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 @@ -186,9 +268,226 @@ INSERT INTO table_name [ ( + + + 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 wether 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 ommitted, 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 a 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. + + + Outputs @@ -197,21 +496,30 @@ INSERT INTO table_name [ ( INSERT oid count + + However, in the event of an ON CONFLICT DO UPDATE clause + (but not in the event of an ON + CONFLICT DO NOTHING 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 +619,65 @@ 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) DO 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: + + INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH') + ON CONFLICT (did) DO NOTHING; + + + + 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 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; + + + + 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: + + -- 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; + + @@ -321,7 +687,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 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 query, diff --git a/doc/src/sgml/trigger.sgml b/doc/src/sgml/trigger.sgml index f94aea174ab..bd0d71e0d92 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 DO UPDATE + clause, it is possible that the effects of a BEFORE insert trigger and + a BEFORE update trigger can both be applied together, 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. @@ -118,6 +121,35 @@ be operated on. + + If an INSERT contains an ON CONFLICT + DO 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 DO UPDATE is specified, regardless of whether or not + any rows were affected by the UPDATE (and + regardless of whether the alternative UPDATE + path was ever taken). An INSERT with an + ON CONFLICT DO 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. + + Trigger functions invoked by per-statement triggers should always return NULL. Trigger functions invoked by per-row -- cgit v1.2.3