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