Note new NULLS NOT DISTINCT on unique index tutorial page

Started by David Gilmanalmost 3 years ago6 messages

davidgilman1@gmail.com

almost 3 years ago

1 attachment(s)

The SQL Language part of the docs has a brief page on unique indexes.
It doesn't mention the new NULLS NOT DISTINCT functionality on unique
indexes and this is a good place to mention it, as it already warns
the user about the old/default behavior.

--
David Gilman
:DG<

Attachments:

0001-Note-NULLS-NOT-DISTINCT-on-unique-index-tutorial.patch.txttext/plain; charset=US-ASCII; name=0001-Note-NULLS-NOT-DISTINCT-on-unique-index-tutorial.patch.txtDownload

From 5cb84716462611b84e6b2fbdb35172ec43b52db8 Mon Sep 17 00:00:00 2001
From: David Gilman <davidgilman1@gmail.com>
Date: Wed, 12 Apr 2023 10:28:09 -0400
Subject: [PATCH] Note NULLS NOT DISTINCT on unique index tutorial

---
 doc/src/sgml/indices.sgml | 8 +++++---
 1 file changed, 5 insertions(+), 3 deletions(-)

diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 0c3fcfd62f8e..0a67e66f5065 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -671,9 +671,11 @@ 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.
+   indexed values are not allowed.  By default, null values in a unique column 
+   are not considered equal, allowing multiple nulls in the column.
+   Use <literal>NULLS NOT DISTINCT</literal> to treat column nulls as equal, 
+   allowing only a single null value in an indexed column. A multicolumn unique index 
+   will only reject cases where all indexed columns are equal in multiple rows.
   </para>
 
   <para>

Corey Huinker

corey.huinker@gmail.com

over 2 years ago

In reply to: David Gilman (#1)

Re: Note new NULLS NOT DISTINCT on unique index tutorial page

On Wed, Apr 12, 2023 at 10:40 AM David Gilman <davidgilman1@gmail.com>
wrote:

The SQL Language part of the docs has a brief page on unique indexes.
It doesn't mention the new NULLS NOT DISTINCT functionality on unique
indexes and this is a good place to mention it, as it already warns
the user about the old/default behavior.

I'm ok with the wording as-is, but perhaps we can phrase it as "distinct"
vs "not equal", thus leaning into the syntax a bit:

By default, null values in a unique column are considered distinct,
allowing multiple nulls in the column.

or maybe

By default, null values in a unique column are considered
<literal>DISTINCT</literal>, allowing multiple nulls in the column.

David Rowley

dgrowleyml@gmail.com

over 2 years ago

In reply to: David Gilman (#1)

1 attachment(s)

Re: Note new NULLS NOT DISTINCT on unique index tutorial page

On Thu, 13 Apr 2023 at 02:40, David Gilman <davidgilman1@gmail.com> wrote:

The SQL Language part of the docs has a brief page on unique indexes.
It doesn't mention the new NULLS NOT DISTINCT functionality on unique
indexes and this is a good place to mention it, as it already warns
the user about the old/default behavior.

I think we should do this and apply it to v15 too.

It seems like a good idea to include the [NULLS [NOT] DISTINCT] in the
syntax synopsis too. Otherwise, the reader of that page is just left
guessing where they'll put NULLS NOT DISTINCT to get the behaviour
you've added the text for.

I've attached an updated patch with that plus 2 very small wording
tweaks to your proposed text.

David

Attachments:

doc_nulls_not_distinct_v2.patchapplication/octet-stream; name=doc_nulls_not_distinct_v2.patchDownload

diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 0c3fcfd62f..c0e3b36647 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -664,16 +664,19 @@ CREATE INDEX test3_desc_index ON test3 (id DESC NULLS LAST);
    Indexes can also be used to enforce uniqueness of a column's value,
    or the uniqueness of the combined values of more than one column.
 <synopsis>
-CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>);
+CREATE UNIQUE INDEX <replaceable>name</replaceable> ON <replaceable>table</replaceable> (<replaceable>column</replaceable> <optional>, ...</optional>) <optional>NULLS <optional>NOT</optional> DISTINCT</optional>;
 </synopsis>
    Currently, only B-tree indexes can be declared unique.
   </para>
 
   <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.
+   indexed values are not allowed.  By default, null values in a unique column
+   are not considered equal, allowing multiple nulls in the column.  Use
+   <literal>NULLS NOT DISTINCT</literal> to treat nulls as equal, thus
+   allowing only a single null value in an indexed column.  A multicolumn
+   unique index will only reject cases where all indexed columns are equal in
+   multiple rows.
   </para>
 
   <para>

David Rowley

dgrowleyml@gmail.com

over 2 years ago

In reply to: Corey Huinker (#2)

Re: Note new NULLS NOT DISTINCT on unique index tutorial page

On Tue, 18 Apr 2023 at 05:01, Corey Huinker <corey.huinker@gmail.com> wrote:

I'm ok with the wording as-is, but perhaps we can phrase it as "distinct" vs "not equal", thus leaning into the syntax a bit:

By default, null values in a unique column are considered distinct, allowing multiple nulls in the column.

or maybe

By default, null values in a unique column are considered <literal>DISTINCT</literal>, allowing multiple nulls in the column.>

I acknowledge your input, but I didn't think either of these was an
improvement over what David suggested. I understand that many people
will know that "SELECT DISTINCT" and "WHERE x IS NOT DISTINCT FROM y"
means treat NULLs equally, but I don't think we should expect the
reader here to know that's what we're talking about. In any case,
we're talking about existing wording here, not something David is
adding.

David

David Gilman

davidgilman1@gmail.com

over 2 years ago

In reply to: David Rowley (#3)

Re: Note new NULLS NOT DISTINCT on unique index tutorial page

The revised patch is good. Please go ahead and commit whatever
phrasing you or the other committers find acceptable. I don't really
have any preferences in how this is exactly phrased, I just think it
should be mentioned in the docs.

On Mon, Apr 17, 2023 at 11:15 PM David Rowley <dgrowleyml@gmail.com> wrote:

On Thu, 13 Apr 2023 at 02:40, David Gilman <davidgilman1@gmail.com> wrote:

The SQL Language part of the docs has a brief page on unique indexes.
It doesn't mention the new NULLS NOT DISTINCT functionality on unique
indexes and this is a good place to mention it, as it already warns
the user about the old/default behavior.

I think we should do this and apply it to v15 too.

It seems like a good idea to include the [NULLS [NOT] DISTINCT] in the
syntax synopsis too. Otherwise, the reader of that page is just left
guessing where they'll put NULLS NOT DISTINCT to get the behaviour
you've added the text for.

I've attached an updated patch with that plus 2 very small wording
tweaks to your proposed text.

David

--
David Gilman
:DG<

David Rowley

dgrowleyml@gmail.com

over 2 years ago

In reply to: David Gilman (#5)

Re: Note new NULLS NOT DISTINCT on unique index tutorial page

On Thu, 20 Apr 2023 at 12:04, David Gilman <davidgilman1@gmail.com> wrote:

The revised patch is good. Please go ahead and commit whatever
phrasing you or the other committers find acceptable. I don't really
have any preferences in how this is exactly phrased, I just think it
should be mentioned in the docs.

Thanks. With that, I admit to further adjusting the wording before I
pushed the result.

David