Add UNIQUE null treatment option
authorPeter Eisentraut <peter@eisentraut.org>
Thu, 3 Feb 2022 10:29:54 +0000 (11:29 +0100)
committerPeter Eisentraut <peter@eisentraut.org>
Thu, 3 Feb 2022 10:48:21 +0000 (11:48 +0100)
commit94aa7cc5f707712f592885995a28e018c7c80488
treed80b1f0a82a43a60e29d9b4960bcff884cdf0183
parentf862d57057fdc73e663fe09d8948ed06b1b71dd7
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 <orlovmg@gmail.com>
Reviewed-by: Pavel Borisov <pashkin.elfe@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/84e5ee1b-387e-9a54-c326-9082674bde78@enterprisedb.com
36 files changed:
doc/src/sgml/catalogs.sgml
doc/src/sgml/ddl.sgml
doc/src/sgml/information_schema.sgml
doc/src/sgml/ref/alter_table.sgml
doc/src/sgml/ref/create_index.sgml
doc/src/sgml/ref/create_table.sgml
src/backend/access/nbtree/nbtinsert.c
src/backend/access/nbtree/nbtsort.c
src/backend/access/nbtree/nbtutils.c
src/backend/catalog/index.c
src/backend/catalog/information_schema.sql
src/backend/catalog/sql_features.txt
src/backend/catalog/toasting.c
src/backend/commands/indexcmds.c
src/backend/nodes/copyfuncs.c
src/backend/nodes/equalfuncs.c
src/backend/nodes/makefuncs.c
src/backend/nodes/outfuncs.c
src/backend/parser/gram.y
src/backend/parser/parse_utilcmd.c
src/backend/utils/adt/ruleutils.c
src/backend/utils/cache/relcache.c
src/backend/utils/sort/tuplesort.c
src/bin/pg_dump/pg_dump.c
src/bin/pg_dump/pg_dump.h
src/bin/psql/describe.c
src/include/catalog/catversion.h
src/include/catalog/pg_index.h
src/include/nodes/execnodes.h
src/include/nodes/makefuncs.h
src/include/nodes/parsenodes.h
src/include/utils/tuplesort.h
src/test/regress/expected/constraints.out
src/test/regress/expected/create_index.out
src/test/regress/sql/constraints.sql
src/test/regress/sql/create_index.sql