From 0d0aeb04c1277edb0733cbf5bf4243c47a439015 Mon Sep 17 00:00:00 2001
From: David Rowley
Date: Thu, 20 Apr 2023 23:51:38 +1200
Subject: Doc: clarify NULLS NOT DISTINCT use in unique indexes
indexes-unique.html mentioned nothing about the availability of NULLS NOT
DISTINCT to modify the NULLs-are-not-equal behavior of unique indexes.
Add this to the synopsis and clarify what it does regarding NULLs.
Author: David Gilman, David Rowley
Reviewed-by: Corey Huinker
Discussion: https://postgr.es/m/CALBH9DDr3NLqzWop1z5uZE-M5G_GYUuAeHFHQeyzFbNd8W0d=Q@mail.gmail.com
Backpatch-through: 15, where NULLS NOT DISTINCT was added
---
doc/src/sgml/indices.sgml | 10 ++++++----
1 file changed, 6 insertions(+), 4 deletions(-)
(limited to 'doc/src')
diff --git a/doc/src/sgml/indices.sgml b/doc/src/sgml/indices.sgml
index 0c3fcfd62f..55122129d5 100644
--- a/doc/src/sgml/indices.sgml
+++ b/doc/src/sgml/indices.sgml
@@ -664,16 +664,18 @@ 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.
-CREATE UNIQUE INDEX name ON table (column , ...);
+CREATE UNIQUE INDEX name ON table (column , ...) NULLS NOT DISTINCT ;
Currently, only B-tree indexes can be declared unique.
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. The
+ NULLS NOT DISTINCT option modifies this and causes the
+ index to treat nulls as equal. A multicolumn unique index will only reject
+ cases where all indexed columns are equal in multiple rows.
--
cgit v1.2.3