From: Laurenz Albe <laurenz.albe@cybertec.at>
To: Kirk Parker <khp@equatoria.us>
To: pgsql-docs@lists.postgresql.org
Subject: Re: Nulls Not Distinct in Unique Indexes secton
Date: Thu, 16 Mar 2023 20:48:58 +0100
Message-ID: <6f76cd51846f0a8f02df91cc252a7ebcf8c6186f.camel@cybertec.at> (raw)
In-Reply-To: <CANwZ8r=hCu6=w=JRER27t9DMwvVngD55DmkU+NX_AZSfkm1KNg@mail.gmail.com>
References: <9C019644-9EA4-4B79-A52C-5C47A5B6B2DF@yesql.se>
<CANwZ8r=hCu6=w=JRER27t9DMwvVngD55DmkU+NX_AZSfkm1KNg@mail.gmail.com>
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
Attachments:
[text/x-patch] 0001-Mention-NULLS-NOT-DISTINCT-in-the-SQL-documentation.patch (1.2K, ../6f76cd51846f0a8f02df91cc252a7ebcf8c6186f.camel@cybertec.at/2-0001-Mention-NULLS-NOT-DISTINCT-in-the-SQL-documentation.patch)
download | inline diff:
From b02fbf9e11c6953269428dd8572e79349d61d646 Mon Sep 17 00:00:00 2001
From: Laurenz Albe <laurenz.albe@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.sgmlindex 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: laurenz.albe@cybertec.at, khp@equatoria.us, pgsql-docs@lists.postgresql.org
Subject: Re: Nulls Not Distinct in Unique Indexes secton
In-Reply-To: <6f76cd51846f0a8f02df91cc252a7ebcf8c6186f.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox