pg.ddx.io pgsql-docs@postgresql.org mailing list archive
help / color / mirror / Atom feedNulls Not Distinct in Unique Indexes secton
2+ messages / 2 participants
[nested] [flat]
* Nulls Not Distinct in Unique Indexes secton
@ 2023-03-14 15:24 Kirk Parker <khp@equatoria.us>
0 siblings, 1 reply; 2+ messages in thread
From: Kirk Parker @ 2023-03-14 15:24 UTC (permalink / raw)
To: pgsql-docs@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Nulls Not Distinct in Unique Indexes secton
@ 2023-03-16 19:48 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Kirk Parker <khp@equatoria.us>
0 siblings, 0 replies; 2+ messages in thread
From: Laurenz Albe @ 2023-03-16 19:48 UTC (permalink / raw)
To: Kirk Parker <khp@equatoria.us>; pgsql-docs@lists.postgresql.org
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.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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-03-16 19:48 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-14 15:24 Nulls Not Distinct in Unique Indexes secton Kirk Parker <khp@equatoria.us>
2023-03-16 19:48 ` Laurenz Albe <laurenz.albe@cybertec.at>
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