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 --- src/backend/parser/parse_utilcmd.c | 3 +++ 1 file changed, 3 insertions(+) (limited to 'src/backend/parser/parse_utilcmd.c') diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 0eea214dd89..99efa26ce4a 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -1581,6 +1581,7 @@ generateClonedIndexStmt(RangeVar *heapRel, Relation source_idx, index->oldCreateSubid = InvalidSubTransactionId; index->oldFirstRelfilenodeSubid = InvalidSubTransactionId; index->unique = idxrec->indisunique; + index->nulls_not_distinct = idxrec->indnullsnotdistinct; index->primary = idxrec->indisprimary; index->transformed = true; /* don't need transformIndexStmt */ index->concurrent = false; @@ -2112,6 +2113,7 @@ transformIndexConstraints(CreateStmtContext *cxt) equal(index->whereClause, priorindex->whereClause) && equal(index->excludeOpNames, priorindex->excludeOpNames) && strcmp(index->accessMethod, priorindex->accessMethod) == 0 && + index->nulls_not_distinct == priorindex->nulls_not_distinct && index->deferrable == priorindex->deferrable && index->initdeferred == priorindex->initdeferred) { @@ -2178,6 +2180,7 @@ transformIndexConstraint(Constraint *constraint, CreateStmtContext *cxt) * DefineIndex will check for it. */ } + index->nulls_not_distinct = constraint->nulls_not_distinct; index->isconstraint = true; index->deferrable = constraint->deferrable; index->initdeferred = constraint->initdeferred; -- cgit v1.2.3