From 94aa7cc5f707712f592885995a28e018c7c80488 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 3 Feb 2022 11:29:54 +0100 Subject: Add UNIQUE null treatment option The SQL standard has been ambiguous about whether null values in unique constraints should be considered equal or not. Different implementations have different behaviors. In the SQL:202x draft, this has been formalized by making this implementation-defined and adding an option on unique constraint definitions UNIQUE [ NULLS [NOT] DISTINCT ] to choose a behavior explicitly. This patch adds this option to PostgreSQL. The default behavior remains UNIQUE NULLS DISTINCT. Making this happen in the btree code is pretty easy; most of the patch is just to carry the flag around to all the places that need it. The CREATE UNIQUE INDEX syntax extension is not from the standard, it's my own invention. I named all the internal flags, catalog columns, etc. in the negative ("nulls not distinct") so that the default PostgreSQL behavior is the default if the flag is false. Reviewed-by: Maxim Orlov Reviewed-by: Pavel Borisov Discussion: https://www.postgresql.org/message-id/flat/84e5ee1b-387e-9a54-c326-9082674bde78@enterprisedb.com --- doc/src/sgml/catalogs.sgml | 13 +++++++++++++ doc/src/sgml/ddl.sgml | 29 ++++++++++++++++++++++++----- doc/src/sgml/information_schema.sgml | 12 ++++++++++++ doc/src/sgml/ref/alter_table.sgml | 4 ++-- doc/src/sgml/ref/create_index.sgml | 13 +++++++++++++ doc/src/sgml/ref/create_table.sgml | 11 ++++++----- 6 files changed, 70 insertions(+), 12 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 7d5b0b1656c..879d2dbce03 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -4265,6 +4265,19 @@ SCRAM-SHA-256$<iteration count>:&l + + + indnullsnotdistinct bool + + + This value is only used for unique indexes. If false, this unique + index will consider null values distinct (so the index can contain + multiple null values in a column, the default PostgreSQL behavior). If + it is true, it will consider null values to be equal (so the index can + only contain one null value in a column). + + + indisprimary bool diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 7cf0f0da3b2..296f3ff59df 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -759,14 +759,33 @@ CREATE TABLE products ( In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. - However, two null values are never considered equal in this + By default, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained - columns. This behavior conforms to the SQL standard, but we have - heard that other SQL databases might not follow this rule. So be - careful when developing applications that are intended to be - portable. + columns. This behavior can be changed by adding the clause NULLS + NOT DISTINCT, like + +CREATE TABLE products ( + product_no integer UNIQUE NULLS NOT DISTINCT, + name text, + price numeric +); + + or + +CREATE TABLE products ( + product_no integer, + name text, + price numeric, + UNIQUE NULLS NOT DISTINCT (product_no) +); + + The default behavior can be specified explicitly using NULLS + DISTINCT. The default null treatment in unique constraints is + implementation-defined according to the SQL standard, and other + implementations have a different behavior. So be careful when developing + applications that are intended to be portable. diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index c5e68c175f9..350c75bc31e 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -6899,6 +6899,18 @@ ORDER BY c.ordinal_position; YES) + + + + nulls_distinct yes_or_no + + + If the constraint is a unique constraint, then YES + if the constraint treats nulls as distinct or NO if + it treats nulls as not distinct, otherwise null for other types of + constraints. + + diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index c8b16c115ed..dee026e3a08 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -103,7 +103,7 @@ WITH ( MODULUS numeric_literal, REM DEFAULT default_expr | GENERATED ALWAYS AS ( generation_expr ) STORED | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] | - UNIQUE index_parameters | + UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters | PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } @@ -113,7 +113,7 @@ WITH ( MODULUS numeric_literal, REM [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | - UNIQUE ( column_name [, ... ] ) index_parameters | + UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml index 89a4d746f6f..91eaaabc90f 100644 --- a/doc/src/sgml/ref/create_index.sgml +++ b/doc/src/sgml/ref/create_index.sgml @@ -24,6 +24,7 @@ PostgreSQL documentation CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] name ] ON [ ONLY ] table_name [ USING method ] ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass [ ( opclass_parameter = value [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ) [ INCLUDE ( column_name [, ...] ) ] + [ NULLS [ NOT ] DISTINCT ] [ WITH ( storage_parameter [= value] [, ... ] ) ] [ TABLESPACE tablespace_name ] [ WHERE predicate ] @@ -334,6 +335,18 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] + + NULLS DISTINCT + NULLS NOT DISTINCT + + + Specifies whether for a unique index, null values should be considered + distinct (not equal). The default is that they are distinct, so that + a unique index could contain multiple null values in a column. + + + + storage_parameter diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index b97bb9ded15..7e4ef312c0e 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -67,7 +67,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI DEFAULT default_expr | GENERATED ALWAYS AS ( generation_expr ) STORED | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] | - UNIQUE index_parameters | + UNIQUE [ NULLS [ NOT ] DISTINCT ] index_parameters | PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } @@ -77,7 +77,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI [ CONSTRAINT constraint_name ] { CHECK ( expression ) [ NO INHERIT ] | - UNIQUE ( column_name [, ... ] ) index_parameters | + UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] @@ -917,8 +917,8 @@ WITH ( MODULUS numeric_literal, REM - UNIQUE (column constraint) - UNIQUE ( column_name [, ... ] ) + UNIQUE [ NULLS [ NOT ] DISTINCT ] (column constraint) + UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] ) INCLUDE ( column_name [, ...]) (table constraint) @@ -934,7 +934,8 @@ WITH ( MODULUS numeric_literal, REM For the purpose of a unique constraint, null values are not - considered equal. + considered equal, unless NULLS NOT DISTINCT is + specified. -- cgit v1.2.3