Subject: pgsql: doc: add examples of creative use of unique expression indexes

From: Bruce Momjian <[email-masked]>
Date: Fri, 27 Dec 2019 19:49:17 +0000

doc:  add examples of creative use of unique expression indexes

Unique expression indexes can constrain data in creative ways, so show
two examples.

Reported-by: Tuomas Leikola

Discussion: https://postgr.es/m/[email-masked]

Backpatch-through: 9.4

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/a9760d0f3cb523336b5fdd9d6c5985e39a8588a1

Modified Files
--------------
doc/src/sgml/indices.sgml | 19 +++++++++++++++++++
1 file changed, 19 insertions(+)

---

From: Tom Lane <[email-masked]>
Date: Fri, 10 Apr 2020 11:30:34 -0400

> https://git.postgresql.org/pg/commitdiff/a9760d0f3cb523336b5fdd9d6c5985e39a8588a1

We had a complaint [1] that this dropped an example into the middle of
two related paragraphs.  I agree with that objection, and also notice
that the extra example broke subsequent references to the "first example"
and "second example".  I'm also unhappy that the other addition that this
commit made was dropped inside Example 11.3; if we're going to use
<example> markup at all, each one ought to be a coherent entity.

On top of that, I don't find that either example actually adds anything
to the discussion, as the same points are being made in the existing
text.  Therefore, I don't think it's worth trying to fix these problems,
and propose just reverting this patch.

			regards, tom lane

[1] https://www.postgresql.org/message-id/158648685043.655.3074746555320970574%40wrigleys.postgresql.org

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 10 Apr 2020 17:48:21 -0400

> [1] https://www.postgresql.org/message-id/158648685043.655.3074746555320970574%40wrigleys.postgresql.org

I agree with your analysis.  I still want to have some mention that
partial indexes can be used to create single-NULL columns, which might
be required for compatibility with other databases.  Attached is an
updated patch which removes the previous commit but adds a mention of
this.

-- 
  Bruce Momjian  <[email-masked]>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 1be209a2fe..3a8f93bac7 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -705,15 +705,6 @@ CREATE INDEX test1_lower_col1_idx ON test1 (lower(col1));
 </programlisting>
   </para>
 
-  <para>
-   Expression indexes also allow control over the scope of unique indexes.
-   For example, this unique index prevents duplicate integer values from
-   being stored in a <type>double precision</type>-typed column:
-<programlisting>
-CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))
-</programlisting>
-  </para>
-
   <para>
    If we were to declare this index <literal>UNIQUE</literal>, it would prevent
    creation of rows whose <literal>col1</literal> values differ only in case,
@@ -953,17 +944,9 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
     WHERE success;
 </programlisting>
     This is a particularly efficient approach when there are few
-    successful tests and many unsuccessful ones.
-   </para>
-
-   <para>
-    This index allows only one null in the indexed column by using a
-    partial index clause to process only null column values, and using
-    an expression index clause to index <literal>true</literal> instead
-    of <literal>null</literal>:
-<programlisting>
-CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
-</programlisting>
+    successful tests and many unsuccessful ones.  Creating a unique
+    index with an <literal>IS NULL</literal> qualification can restrict
+    a column to a single <literal>NULL</literal> value.
    </para>
   </example>

---

From: Tom Lane <[email-masked]>
Date: Fri, 10 Apr 2020 19:21:29 -0400

> updated patch which removes the previous commit but adds a mention of

The single-null thing is probably a useful example, but please make
it an actual separate example, or at least its own para outside the
existing <example> sections.

Also, the existing example demonstrating that seems overcomplicated;
why not just

    create unique index ... (1) where (foo is null);

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 10 Apr 2020 20:17:09 -0400

>     create unique index ... (1) where (foo is null);

I ended up using "true" since that is ony one byte;  patch attached.

index 1be209a2fe..e8205a07b5 100644
 
-  <para>
-   Expression indexes also allow control over the scope of unique indexes.
-   For example, this unique index prevents duplicate integer values from
-   being stored in a <type>double precision</type>-typed column:
-<programlisting>
-CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))
-</programlisting>
-  </para>
-
   <para>
    If we were to declare this index <literal>UNIQUE</literal>, it would prevent
    creation of rows whose <literal>col1</literal> values differ only in case,
@@ -956,16 +947,17 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
     successful tests and many unsuccessful ones.
    </para>
 
-   <para>
-    This index allows only one null in the indexed column by using a
-    partial index clause to process only null column values, and using
-    an expression index clause to index <literal>true</literal> instead
-    of <literal>null</literal>:
+  </example>
+
+  <para>
+   This index allows only one null in the indexed column by using a
+   partial index clause to process only null column values, and using
+   an expression index clause to index <literal>true</literal> instead
+   of <literal>null</literal>:
 <programlisting>
-CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
+CREATE UNIQUE INDEX tests_target_one_null ON tests ((true)) WHERE target IS NULL;
 </programlisting>
-   </para>
-  </example>
+  </para>
 
   <para>
    Finally, a partial index can also be used to override the system's

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 11 Apr 2020 08:25:32 -0400

> I ended up using "true" since that is ony one byte;  patch attached.

[ thread moved to docs]

I now remember that I wrote the first IS NULL in:

  CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;

in hope that if someone is looking for the null value in the column, the
IS NULL would allow the index to be used to find it, while 1 or true
would not.

Also, I think the most popular use for this ability would be for
multi-column indexes where you want only one NULL value for a
combination of columns, e.g.:

  CREATE UNIQUE INDEX tests_target_one_null ON test (x, (y IS NULL)) WHERE y IS NULL;

I have added that.  It also hows the use of columns and expressions in
the same index.  Proposed patch attached.

index 1be209a2fe..2790fb2a89 100644
 
-  <para>
@@ -956,16 +947,22 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
 
-   <para>
-    This index allows only one null in the indexed column by using a
-    partial index clause to process only null column values, and using
-    an expression index clause to index <literal>true</literal> instead
-    of <literal>null</literal>:
+  </example>
+
+  <para>
+   This index allows only one null in the indexed column by using a
+   partial index clause to process only null column values, and using
+   an expression index clause to index <literal>true</literal> instead
+   of <literal>null</literal>:
 <programlisting>
 CREATE UNIQUE INDEX tests_target_one_null ON tests ((target IS NULL)) WHERE target IS NULL;
 </programlisting>
-   </para>
-  </example>
+   This index allows only one null value for each value in the first
+   referenced column:
+<programlisting>
+CREATE UNIQUE INDEX tests_combo_one_null ON tests (col1, (target IS NULL)) WHERE target IS NULL;
+</programlisting>
+  </para>
 
   <para>

---

From: Tom Lane <[email-masked]>
Date: Mon, 20 Apr 2020 16:21:32 -0400

[ sorry, I'd lost track of this thread ]

> IS NULL would allow the index to be used to find it, while 1 or true

Well, that's not the case:

regression=# create index tenk1_null_index on tenk1((1)) where ten is null;
CREATE INDEX
regression=# explain select * from tenk1 where ten is null;
                                   QUERY PLAN                                   
--------------------------------------------------------------------------------
 Index Scan using tenk1_null_index on tenk1  (cost=0.12..8.14 rows=1 width=244)
(1 row)

(Maybe it was true at some time in the past, but not any more.)

Also, it complicates the example, and since you didn't explain the
reason for the complication, I think it's pretty confusing.

But really I still don't see the need for these additional examples
at all.  It's especially weird that what you want to do is have
some examples on that page have <example> markup and others not.

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Mon, 20 Apr 2020 20:31:29 -0400

> regression=# explain select * from tenk1 where ten is null;

OK, seems like only you and I care about this issue, which I take to
mean that we should minimize what we are adding here.  What the attached
patch does is to remove the previous commit, and just add a sentence to
the last example to mention the ability restrict a column to a single
NULL.

index 1be209a2fe..13285bb0dd 100644
 
-  <para>
-   Expression indexes also allow control over the scope of unique indexes.
-   For example, this unique index prevents duplicate integer values from
-   being stored in a <type>double precision</type>-typed column:
-<programlisting>
-CREATE UNIQUE INDEX test1_uniq_int ON tests ((floor(double_col)))
-</programlisting>
-  </para>
-
   <para>
    If we were to declare this index <literal>UNIQUE</literal>, it would prevent
    creation of rows whose <literal>col1</literal> values differ only in case,
@@ -953,18 +944,11 @@ CREATE UNIQUE INDEX tests_success_constraint ON tests (subject, target)
     WHERE success;
 </programlisting>
     This is a particularly efficient approach when there are few
-    successful tests and many unsuccessful ones.
+    successful tests and many unsuccessful ones.  It is also possible to
+    allow only one null in a column by creating a unique partial index
+    with an <literal>IS NULL</literal> restriction.
    </para>
 
-   <para>
 
   <para>

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 21 May 2020 19:49:41 -0400

> the last example to mention the ability restrict a column to a single

Patch applied.