On Tue, 2023-03-14 at 08:24 -0700, Kirk Parker wrote:
> The Unique Indexes section
> (https://www.postgresql.org/docs/15/indexes-unique.html)
> does not mention the new NULLS [ NOT ] DISTINCT capability of indexes
> (https://www.postgresql.org/docs/15/sql-createindex.html), and it probably
> should.
>
> Specifically, it has the exact wording from previous versions (emphasis
> added):
>
> When an index is declared unique, multiple table
> rows with equal indexed values are not allowed.
> *Null values are not considered equal*.
>
> We should consider adding "unless the NULLS NOT DISTINCT clause is used when
> creating the index", or something to that effect.
+1
Here is a patch for that.
Yours,
Laurenz Albe
From b02fbf9e11c6953269428dd8572e79349d61d646 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.a...@cybertec.at>
Date: Thu, 16 Mar 2023 20:47:20 +0100
Subject: [PATCH] Mention NULLS NOT DISTINCT in the SQL documentation
Commit 94aa7cc5f7 forgot one place in the documentation that
still claimed unconditionally that NULLs are considered equal.
Per report by Kirk Parker.
---
doc/src/sgml/indices.sgml | 5 +++--
1 file changed, 3 insertions(+), 2 deletions(-)
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 0c3fcfd62f..f1c15fcc0b 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -672,8 +672,9 @@ CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</repla
<para>
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.
+ equal, unless the index is created with the <literal>NULLS NOT
+ DISTINCT</literal> clause. A multicolumn unique index will only
+ reject cases where all indexed columns are equal in multiple rows.
</para>
<para>
--
2.39.2
