From: Peter Eisentraut <peter@eisentraut.org>
To: pgsql-committers@lists.postgresql.org
Subject: pgsql: Add UNIQUE null treatment option
Date: Thu, 03 Feb 2022 10:51:53 +0000
Message-ID: <E1nFZiT-0003JZ-T8@gemulon.postgresql.org> (raw)
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
Branch
------
master
Details
-------
https://git.postgresql.org/pg/commitdiff/94aa7cc5f707712f592885995a28e018c7c80488
Modified Files
--------------
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 +++---
src/backend/access/nbtree/nbtinsert.c | 6 +--
src/backend/access/nbtree/nbtsort.c | 15 +++++++-
src/backend/access/nbtree/nbtutils.c | 7 ++++
src/backend/catalog/index.c | 7 ++++
src/backend/catalog/information_schema.sql | 9 ++++-
src/backend/catalog/sql_features.txt | 1 +
src/backend/catalog/toasting.c | 1 +
src/backend/commands/indexcmds.c | 3 +-
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/equalfuncs.c | 2 +
src/backend/nodes/makefuncs.c | 3 +-
src/backend/nodes/outfuncs.c | 2 +
src/backend/parser/gram.y | 47 ++++++++++++++---------
src/backend/parser/parse_utilcmd.c | 3 ++
src/backend/utils/adt/ruleutils.c | 23 +++++++----
src/backend/utils/cache/relcache.c | 1 +
src/backend/utils/sort/tuplesort.c | 8 +++-
src/bin/pg_dump/pg_dump.c | 19 ++++++++--
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/psql/describe.c | 19 ++++++++--
src/include/catalog/catversion.h | 2 +-
src/include/catalog/pg_index.h | 1 +
src/include/nodes/execnodes.h | 1 +
src/include/nodes/makefuncs.h | 2 +-
src/include/nodes/parsenodes.h | 2 +
src/include/utils/tuplesort.h | 1 +
src/test/regress/expected/constraints.out | 23 +++++++++++
src/test/regress/expected/create_index.out | 61 ++++++++++++++++++++++++++++++
src/test/regress/sql/constraints.sql | 14 +++++++
src/test/regress/sql/create_index.sql | 37 ++++++++++++++++++
36 files changed, 348 insertions(+), 57 deletions(-)
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-committers@postgresql.org
Cc: peter@eisentraut.org
Subject: Re: pgsql: Add UNIQUE null treatment option
In-Reply-To: <E1nFZiT-0003JZ-T8@gemulon.postgresql.org>
References: <E1nFZiT-0003JZ-T8@gemulon.postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox