diff options
| author | David Rowley <drowley@postgresql.org> | 2023-04-20 23:51:38 +1200 |
|---|---|---|
| committer | David Rowley <drowley@postgresql.org> | 2023-04-20 23:51:38 +1200 |
| commit | 0d0aeb04c1277edb0733cbf5bf4243c47a439015 (patch) | |
| tree | be1b87ed4c76972e58447963dfabbd8cf9ae71f1 /doc | |
| parent | c1cc4e688b60090f194017c6e15e330a725fba3e (diff) | |
| download | postgresql-0d0aeb04c1277edb0733cbf5bf4243c47a439015.tar.gz | |
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
Diffstat (limited to 'doc')
| -rw-r--r-- | doc/src/sgml/indices.sgml | 10 |
1 files changed, 6 insertions, 4 deletions
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. <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. The + <literal>NULLS NOT DISTINCT</literal> 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. </para> <para> |