Attached is a patch that makes a few additional changes to the INSERT
documentation. These changes are mostly around formatting. I also
point out that unique index inference is mandatory when using unique
indexes and specifying a conflict target (because unique indexes are
not formally constraints).
--
Peter Geoghegan
From 63336a0bee877eefaf3ef1ce9e50a39bdbc766a7 Mon Sep 17 00:00:00 2001
From: Peter Geoghegan <peter.geoghega...@gmail.com>
Date: Thu, 24 Dec 2015 13:15:27 -0800
Subject: [PATCH] Minor copy-editing of INSERT documentation
Fix a few minor issues that remained after the major post-commit
overhaul of ON CONFLICT.
---
doc/src/sgml/ref/insert.sgml | 56 ++++++++++++++++++++++++--------------------
1 file changed, 31 insertions(+), 25 deletions(-)
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index e710cf4..fe000d5 100644
--- a/doc/src/sgml/ref/insert.sgml
+++ b/doc/src/sgml/ref/insert.sgml
@@ -300,14 +300,15 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
class="PARAMETER">table_name</replaceable> unique indexes that,
without regard to order, contain exactly the
<parameter>conflict_target</parameter>-specified
- columns/expressions are inferred (chosen) as arbiter indexes. If
- an <replaceable class="PARAMETER">index_predicate</replaceable> is
- specified, it must, as a further requirement for inference,
- satisfy arbiter indexes. Note that this means a non-partial
- unique index (a unique index without a predicate) will be inferred
- (and thus used by <literal>ON CONFLICT</literal>) if such an index
- satisfying every other criteria is available. If an attempt at
- inference is unsuccessful, an error is raised.
+ columns/expressions at least once are inferred (chosen) as arbiter
+ indexes. If an <replaceable
+ class="PARAMETER">index_predicate</replaceable> is specified, it
+ must, as a further requirement for inference, satisfy arbiter
+ indexes. Note that this means a non-partial unique index (a
+ unique index without a predicate) will be inferred (and thus used
+ by <literal>ON CONFLICT</literal>) if such an index satisfying
+ every other criteria is available. If an attempt at inference is
+ unsuccessful, an error is raised.
</para>
<para>
@@ -493,7 +494,10 @@ INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replac
correctly when the underlying index is replaced by another more
or less equivalent index in an overlapping way, for example when
using <literal>CREATE UNIQUE INDEX ... CONCURRENTLY</literal>
- before dropping the index being replaced.
+ before dropping the constraint being replaced. Note that unique
+ index inference <emphasis>must</emphasis> be used to choose
+ unique indexes as arbiters, since unique indexes are not formally
+ classified as constraints.
</para>
</tip>
@@ -627,11 +631,11 @@ INSERT INTO employees_log SELECT *, current_timestamp FROM upd;
</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 the special
- <varname>excluded</> table is used to reference values originally
- proposed for insertion:
+ Insert or update new distributors as appropriate. Example assumes
+ a unique index or constraint has been defined that constrains
+ values appearing in the <literal>did</literal> column. Note that
+ the special <varname>excluded</> table is used to reference values
+ originally proposed for insertion:
<programlisting>
INSERT INTO distributors (did, dname)
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc')
@@ -641,9 +645,10 @@ INSERT INTO distributors (did, dname)
<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:
+ column or columns after per-row <literal>BEFORE INSERT</literal>
+ triggers fire) exists. Example assumes a unique index or
+ constraint 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;
@@ -651,10 +656,11 @@ INSERT INTO distributors (did, dname) VALUES (7, 'Redline GmbH')
</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):
+ a unique index or constraint has been defined that constrains
+ values appearing in the <literal>did</literal> column. A
+ <literal>WHERE</> clause is used, limiting which rows are actually
+ updated when the <literal>UPDATE</literal> action is taken (any
+ existing rows that are not updated are still 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')
@@ -670,11 +676,11 @@ INSERT INTO distributors (did, dname) VALUES (9, 'Antwerp Design')
</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>DO NOTHING</literal>. Example assumes a unique index or
+ constraint 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>:
+ <literal>is_active</literal> <type>boolean</type> 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
--
1.9.1
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
