Subject: uniqueness and null could benefit from a hint for dba

From: PG Doc comments form <[email-masked]>
Date: Wed, 04 Sep 2019 13:12:35 +0000

The following documentation comment has been logged on the website:

Page: https://www.postgresql.org/docs/11/indexes-unique.html
Description:

Sometimes it is convenient to create an unique index that considers NULL
values equal. Designating a "zero" value for those rows might not be
feasible, for example due to a foreign key.

The documentation currently only states that unique indexes do not consider
NULLs equal. It might be good to offer workarounds, like indexing a coalesce
function, if scans are not the reason for the index, but the uniqueness
constraint.

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 27 Sep 2019 12:37:47 -0400

> function, if scans are not the reason for the index, but the uniqueness

I did write a blog entry about this:

	https://momjian.us/main/blogs/pgblog/2017.html#April_3_2017

Does that help?

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

+ As you are, so once was I.  As I am, so you will be. +
+                      Ancient Roman grave inscription +

---

From: Tuomas Leikola <[email-masked]>
Date: Wed, 23 Oct 2019 14:35:02 +0300

That is a nice design. You can create a regular unique index where
columns(s)s are not null and then filtered index(es) for the cases where
some of the unique columns are null.

However my point, specifically, was that if the document in question would
have offered alternative solutions, I personally would have been saved from
some frustration and an exercise in bad index design (I had 5 nullables
that need uniqueness for the null as well). Maybe it would help someone
else.

-- 
- Tuomas

---

From: Bruce Momjian <[email-masked]>
Date: Thu, 24 Oct 2019 10:31:44 -0400

On Wed, Oct 23, 2019 at 02:35:02PM +0300, Tuomas Leikola wrote:
> That is a nice design. You can create a regular unique index where columns(s)s
> are not null and then filtered index(es) for the cases where some of the unique
> columns are null.
> 
> However my point, specifically, was that if the document in question would have
> offered alternative solutions, I personally would have been saved from some
> frustration and an exercise in bad index design (I had 5 nullables that need
> uniqueness for the null as well). Maybe it would help someone else.

Uh, I am wondering if it is just too details for our docs.  Can you
think of some text and its location?

---

From: Tuomas Leikola <[email-masked]>
Date: Tue, 29 Oct 2019 14:00:38 +0200

> think of some text and its location?
"Unique indexes on functions can be used to create special types of
constraints, like considering unique values equal (coalesce) or only
allowing a single unique integer value of a float column (floor). A
filtered unique index only enforces uniqueness on the subset of rows that
match the filter."

I guess this would append to the paragraph "When an index is declared
unique, multiple table rows with equal indexed values are not allowed. Null
values are not considered equal. A multicolumn unique index will only
reject cases where all indexed columns are equal in multiple rows.". Links
to the mentioned keywords and concepts would of course be helpful, but not
strictly necessary.

I also thought of mentioning the caveat of (un)scannability of function or
filtered indexes but maybe that is a bit much.

-- 
- Tuomas

---

From: Bruce Momjian <[email-masked]>
Date: Tue, 5 Nov 2019 12:13:06 -0500

On Tue, Oct 29, 2019 at 02:00:38PM +0200, Tuomas Leikola wrote:
> constraints, like considering unique values equal (coalesce) or only allowing a
> single unique integer value of a float column (floor). A filtered unique index
> only enforces uniqueness on the subset of rows that match the filter."
> 
> I guess this would append to the paragraph "When an index is declared unique,
> multiple table rows with equal indexed values are not allowed. Null values are
> not considered equal. A multicolumn unique index will only reject cases where
> all indexed columns are equal in multiple rows.". Links to the mentioned
> keywords and concepts would of course be helpful, but not strictly necessary.
> 

I have reviewed our documentation and found approrpiate places to
mention your floor() example, and my IS NULL example.  Patch attached.

+ 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
new file mode 100644
index 95c0a19..c54bf0d
*** a/doc/src/sgml/indices.sgml
--- b/doc/src/sgml/indices.sgml
*************** CREATE INDEX test1_lower_col1_idx ON tes
*** 706,711 ****
--- 706,720 ----
    </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,
     as well as rows whose <literal>col1</literal> values are actually identical.
*************** CREATE UNIQUE INDEX tests_success_constr
*** 946,951 ****
--- 955,970 ----
      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>
+    </para>
    </example>
  
    <para>

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 27 Dec 2019 14:49:27 -0500

> mention your floor() example, and my IS NULL example.  Patch attached.

Patch applied back through 9.4.  Thanks.