diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/fdwhandler.sgml | 7 | ||||
| -rw-r--r-- | doc/src/sgml/keywords.sgml | 7 | ||||
| -rw-r--r-- | doc/src/sgml/mvcc.sgml | 23 | ||||
| -rw-r--r-- | doc/src/sgml/plpgsql.sgml | 14 | ||||
| -rw-r--r-- | doc/src/sgml/postgres-fdw.sgml | 8 | ||||
| -rw-r--r-- | doc/src/sgml/protocol.sgml | 13 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_policy.sgml | 63 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_rule.sgml | 6 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 4 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_trigger.sgml | 5 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_view.sgml | 9 | ||||
| -rw-r--r-- | doc/src/sgml/ref/insert.sgml | 403 | ||||
| -rw-r--r-- | doc/src/sgml/trigger.sgml | 48 |
13 files changed, 550 insertions, 60 deletions
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. </para> + <para> + <command>INSERT</> with an <literal>ON CONFLICT</> clause does not + support specifying the conflict target, as remote constraints are not + locally known. This in turn implies that <literal>ON CONFLICT DO + UPDATE</> is not supported, since the specification is mandatory there. + </para> + </sect1> </chapter> 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 @@ -854,6 +854,13 @@ <entry></entry> </row> <row> + <entry><token>CONFLICT</token></entry> + <entry>non-reserved</entry> + <entry></entry> + <entry></entry> + <entry></entry> + </row> + <row> <entry><token>CONNECT</token></entry> <entry></entry> <entry>reserved</entry> 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 @@ </para> <para> - 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 + <command>INSERT</command> with an <literal>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 <command>INSERT + </command>, the <command>UPDATE</command> clause will affect that row, + even though possibly <emphasis>no</> version of that row is + conventionally visible to the command. + </para> + + <para> + <command>INSERT</command> with an <literal>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 <command>INSERT</command> snapshot. Again, this is only + the case in Read Committed mode. + </para> + + <para> + 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; <para> This example uses exception handling to perform either - <command>UPDATE</> or <command>INSERT</>, as appropriate: + <command>UPDATE</> or <command>INSERT</>, as appropriate. It is + recommended that applications use <command>INSERT</> with + <literal>ON CONFLICT DO UPDATE</> rather than actually using + this pattern. This example serves primarily to illustrate use of + <application>PL/pgSQL</application> control flow structures: <programlisting> CREATE TABLE db (a INT PRIMARY KEY, b TEXT); @@ -3852,9 +3856,11 @@ ASSERT <replaceable class="parameter">condition</replaceable> <optional> , <repl <command>INSERT</> and <command>UPDATE</> operations, the return value should be <varname>NEW</>, which the trigger function may modify to support <command>INSERT RETURNING</> and <command>UPDATE RETURNING</> - (this will also affect the row value passed to any subsequent triggers). - For <command>DELETE</> operations, the return value should be - <varname>OLD</>. + (this will also affect the row value passed to any subsequent triggers, + or passed to a special <varname>EXCLUDED</> alias reference within + an <command>INSERT</> statement with an <literal>ON CONFLICT DO + UPDATE</> clause). For <command>DELETE</> operations, the return + value should be <varname>OLD</>. </para> <para> 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 @@ -69,6 +69,14 @@ </para> <para> + Note that <filename>postgres_fdw</> currently lacks support for + <command>INSERT</command> statements with an <literal>ON CONFLICT DO + UPDATE</> clause. However, the <literal>ON CONFLICT DO NOTHING</> + clause is supported, provided a unique index inference specification + is omitted. + </para> + + <para> 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 <filename>postgres_fdw</> 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) <literal>INSERT <replaceable>oid</replaceable> <replaceable>rows</replaceable></literal>, where <replaceable>rows</replaceable> is the number of rows - inserted. <replaceable>oid</replaceable> is the object ID - of the inserted row if <replaceable>rows</replaceable> is 1 - and the target table has OIDs; + inserted. However, if and only if <literal>ON CONFLICT + UPDATE</> is specified, then the tag is <literal>UPSERT + <replaceable>oid</replaceable> + <replaceable>rows</replaceable></literal>, where + <replaceable>rows</replaceable> is the number of rows inserted + <emphasis>or updated</emphasis>. + <replaceable>oid</replaceable> is the object ID of the + inserted row if <replaceable>rows</replaceable> is 1 and the + target table has OIDs, and (for the <literal>UPSERT</literal> + tag), the row was actually inserted rather than updated; otherwise <replaceable>oid</replaceable> is 0. </para> 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 <replaceable class="parameter">name</replaceable> ON <replaceable Policies can be applied for specific commands or for specific roles. The default for newly created policies is that they apply for all commands and roles, unless otherwise specified. If multiple policies apply to a given - query, they will be combined using OR. 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). + query, they will be combined using OR (although <literal>ON CONFLICT DO + UPDATE</> and <literal>INSERT</> policies are not combined in this way, but + rather enforced as noted at each stage of <literal>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). </para> <para> @@ -263,6 +265,12 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable as it only ever applies in cases where records are being added to the relation. </para> + <para> + Note that <literal>INSERT</literal> with <literal>ON CONFLICT DO + UPDATE</literal> requires that any <literal>INSERT</literal> policy + WITH CHECK expression passes for any rows appended to the relation by + the INSERT path only. + </para> </listitem> </varlistentry> @@ -271,22 +279,39 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable <listitem> <para> Using <literal>UPDATE</literal> for a policy means that it will apply - to <literal>UPDATE</literal> commands. As <literal>UPDATE</literal> - involves pulling an existing record and then making changes to some - portion (but possibly not all) of the record, the - <literal>UPDATE</literal> policy accepts both a USING expression and - a WITH CHECK expression. The USING expression will be used to - determine which records the <literal>UPDATE</literal> command will - see to operate against, while the <literal>WITH CHECK</literal> - expression defines what rows are allowed to be added back into the - relation (similar to the <literal>INSERT</literal> policy). - Any rows whose resulting values do not pass the - <literal>WITH CHECK</literal> expression will cause an ERROR and the - entire command will be aborted. Note that if only a - <literal>USING</literal> clause is specified then that clause will be - used for both <literal>USING</literal> and + to <literal>UPDATE</literal> commands (or auxiliary <literal>ON + CONFLICT DO UPDATE</literal> clauses of <literal>INSERT</literal> + commands). As <literal>UPDATE</literal> involves pulling an existing + record and then making changes to some portion (but possibly not all) + of the record, the <literal>UPDATE</literal> policy accepts both a + <literal>USING</literal> expression and a <literal>WITH CHECK</literal> + expression. The <literal>USING</literal> expression will be used to + determine which records the <literal>UPDATE</literal> command will see + to operate against, while the <literal>WITH CHECK</literal> expression + defines what rows are allowed to be added back into the relation + (similar to the <literal>INSERT</literal> policy). Any rows whose + resulting values do not pass the <literal>WITH CHECK</literal> + expression will cause an ERROR and the entire command will be aborted. + Note that if only a <literal>USING</literal> clause is specified then + that clause will be used for both <literal>USING</literal> and <literal>WITH CHECK</literal> cases. </para> + <para> + Note, however, that <literal>INSERT</literal> with <literal>ON CONFLICT + DO UPDATE</literal> requires that an <literal>UPDATE</literal> policy + <literal>USING</literal> expression always be enforced as a + <literal>WITH CHECK</literal> expression. This + <literal>UPDATE</literal> policy must always pass when the + <literal>UPDATE</literal> path is taken. Any existing row that + necessitates that the <literal>UPDATE</literal> path be taken must pass + the (UPDATE or ALL) <literal>USING</literal> qualifications (combined + using <literal>OR</literal>), which are always enforced as WTIH CHECK + options in this context (the <literal>UPDATE</literal> path will + <emphasis>never</> be silently avoided; an error will be thrown + instead). Finally, the final row appended to the relation must pass + any <literal>WITH CHECK</literal> options that a conventional + <literal>UPDATE</literal> is required to pass. + </para> </listitem> </varlistentry> 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 <replaceable class="parameter">name</replaceable> AS <para> The event is one of <literal>SELECT</literal>, <literal>INSERT</literal>, <literal>UPDATE</literal>, or - <literal>DELETE</literal>. + <literal>DELETE</literal>. Note that an + <command>INSERT</command> containing an <literal>ON + CONFLICT</literal> clause cannot be used on tables that have + either <literal>INSERT</literal> or <literal>UPDATE</literal> + rules. Consider using an updatable view instead. </para> </listitem> </varlistentry> 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 <literal>EXCLUDE</>, and <literal>REFERENCES</> (foreign key) constraints accept this clause. <literal>NOT NULL</> and <literal>CHECK</> constraints are not - deferrable. + deferrable. Note that deferrable constraints cannot be used as + conflict arbitrators in an <command>INSERT</command> statement that + includes an <literal>ON CONFLICT DO UPDATE</> clause. </para> </listitem> </varlistentry> 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 <replaceable class="PARAMETER">name</replaceable> 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 <literal>FOR - EACH STATEMENT</literal> triggers). + EACH STATEMENT</literal> triggers). Note that with an + <command>INSERT</command> with an <literal>ON CONFLICT DO UPDATE</> + clause, both <command>INSERT</command> and + <command>UPDATE</command> statement level trigger will be fired. </para> <para> 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 <command>INSERT</>, <command>UPDATE</> or <command>DELETE</> statement on the view into the corresponding statement on the underlying base - relation. + relation. <command>INSERT</> statements that have an <literal>ON + CONFLICT UPDATE</> clause are fully supported. </para> <para> @@ -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 <command>INSERT</> command can potentially insert base-relation rows that do not satisfy the <literal>WHERE</> condition and thus are not - visible through the view. The <literal>CHECK OPTION</> may be used to - prevent <command>INSERT</> and <command>UPDATE</> commands from creating + visible through the view (<literal>ON CONFLICT UPDATE</> may + similarly affect an existing row not visible through the view). + The <literal>CHECK OPTION</> may be used to prevent + <command>INSERT</> and <command>UPDATE</> commands from creating such rows that are not visible through the view. </para> 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 <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</>, 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 <command>INSERT</command>, <command>UPDATE</command>, or <command>DELETE</command> operation, either once per modified row, - or once per <acronym>SQL</acronym> statement. - <command>UPDATE</command> triggers can moreover be set to fire only if - certain columns are mentioned in the <literal>SET</literal> clause of the - <command>UPDATE</command> statement. - Triggers can also fire for <command>TRUNCATE</command> 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 <acronym>SQL</acronym> statement. If an + <command>INSERT</command> contains an <literal>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 <varname>EXCLUDED</> column appears. <command>UPDATE</command> + triggers can moreover be set to fire only if certain columns are + mentioned in the <literal>SET</literal> clause of the + <command>UPDATE</command> statement. Triggers can also fire for + <command>TRUNCATE</command> 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. </para> <para> @@ -119,6 +122,35 @@ </para> <para> + If an <command>INSERT</command> contains an <literal>ON CONFLICT + DO UPDATE</> clause, it is possible that the effects of all + row-level <literal>BEFORE</> <command>INSERT</command> triggers + and all row-level BEFORE <command>UPDATE</command> triggers can + both be applied in a way that is apparent from the final state of + the updated row, if an <varname>EXCLUDED</> column is referenced. + There need not be an <varname>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 <literal>BEFORE</> <command>INSERT</command> and + <literal>BEFORE</> <command>UPDATE</command> 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 + <command>UPDATE</command> triggers are executed when <literal>ON + CONFLICT DO UPDATE</> is specified, regardless of whether or not + any rows were affected by the <command>UPDATE</command> (and + regardless of whether the alternative <command>UPDATE</command> + path was ever taken). An <command>INSERT</command> with an + <literal>ON CONFLICT DO UPDATE</> clause will execute + statement-level <literal>BEFORE</> <command>INSERT</command> + triggers first, then statement-level <literal>BEFORE</> + <command>UPDATE</command> triggers, followed by statement-level + <literal>AFTER</> <command>UPDATE</command> triggers and finally + statement-level <literal>AFTER</> <command>INSERT</command> + triggers. + </para> + + <para> Trigger functions invoked by per-statement triggers should always return <symbol>NULL</symbol>. Trigger functions invoked by per-row triggers can return a table row (a value of |