UNIQUE null treatment option
Started by Peter Eisentrautover 4 years ago14 messages
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 apparently 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. But perhaps the double
negatives make some code harder to read.)
Attachments:
0001-Add-UNIQUE-null-treatment-option.patchtext/plain; charset=UTF-8; name=0001-Add-UNIQUE-null-treatment-option.patch; x-mac-creator=0; x-mac-type=0Download
From 14bd23b4f164c4298262e7fbfec1a49292d16e27 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Fri, 27 Aug 2021 14:31:46 +0200
Subject: [PATCH] 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.
XXX 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. But perhaps the double
negatives make some code harder to read.
---
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 | 10 ++---
src/backend/access/nbtree/nbtsort.c | 15 ++++++-
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 | 9 +++-
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/psql/describe.c | 30 ++++++++++---
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/create_index.out | 51 ++++++++++++++++++++++
src/test/regress/input/constraints.source | 14 ++++++
src/test/regress/output/constraints.source | 23 ++++++++++
src/test/regress/sql/create_index.sql | 36 +++++++++++++++
34 files changed, 332 insertions(+), 58 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2b2c70a26e..fd49738d4f 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4256,6 +4256,19 @@ <title><structname>pg_index</structname> Columns</title>
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>indnullsnotdistinct</structfield> <type>bool</type>
+ </para>
+ <para>
+ 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).
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>indisprimary</structfield> <type>bool</type>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index e0ffb020bf..815a2e23f9 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -759,14 +759,33 @@ <title>Unique Constraints</title>
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 <literal>NULLS
+ NOT DISTINCT</literal>, like
+<programlisting>
+CREATE TABLE products (
+ product_no integer UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis>,
+ name text,
+ price numeric
+);
+</programlisting>
+ or
+<programlisting>
+CREATE TABLE products (
+ product_no integer,
+ name text,
+ price numeric,
+ UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis> (product_no)
+);
+</programlisting>
+ The default behavior can be specified explicitly using <literal>NULLS
+ DISTINCT</literal>. 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.
</para>
</sect2>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index c5e68c175f..350c75bc31 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -6899,6 +6899,18 @@ <title><structname>table_constraints</structname> Columns</title>
<literal>YES</literal>)
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>nulls_distinct</structfield> <type>yes_or_no</type>
+ </para>
+ <para>
+ If the constraint is a unique constraint, then <literal>YES</literal>
+ if the constraint treats nulls as distinct or <literal>NO</literal> if
+ it treats nulls as not distinct, otherwise null for other types of
+ constraints.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 81291577f8..0edf3563d3 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -103,7 +103,7 @@
DEFAULT <replaceable>default_expr</replaceable> |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
- UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
@@ -113,7 +113,7 @@
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index cc484d5b39..453794b5d2 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -24,6 +24,7 @@
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> [ ( <replaceable class="parameter">opclass_parameter</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+ [ NULLS [ NOT ] DISTINCT ]
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
@@ -331,6 +332,18 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>NULLS DISTINCT</literal></term>
+ <term><literal>NULLS NOT DISTINCT</literal></term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">storage_parameter</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 473a0a4aeb..35fc6324a5 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -67,7 +67,7 @@
DEFAULT <replaceable>default_expr</replaceable> |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
- UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
@@ -77,7 +77,7 @@
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
@@ -913,8 +913,8 @@ <title>Parameters</title>
</varlistentry>
<varlistentry>
- <term><literal>UNIQUE</literal> (column constraint)</term>
- <term><literal>UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
+ <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ]</literal> (column constraint)</term>
+ <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
<optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
<listitem>
@@ -930,7 +930,8 @@ <title>Parameters</title>
<para>
For the purpose of a unique constraint, null values are not
- considered equal.
+ considered equal, unless <literal>NULLS NOT DISTINCT</literal> is
+ specified.
</para>
<para>
diff --git a/src/backend/access/nbtree/nbtinsert.c b/src/backend/access/nbtree/nbtinsert.c
index 6ac205c98e..f7fc015ae3 100644
--- a/src/backend/access/nbtree/nbtinsert.c
+++ b/src/backend/access/nbtree/nbtinsert.c
@@ -111,7 +111,7 @@ _bt_doinsert(Relation rel, IndexTuple itup,
if (checkingunique)
{
- if (!itup_key->anynullkeys)
+ if (!itup_key->anynullkeys || rel->rd_index->indnullsnotdistinct)
{
/* No (heapkeyspace) scantid until uniqueness established */
itup_key->scantid = NULL;
@@ -397,9 +397,9 @@ _bt_search_insert(Relation rel, BTInsertState insertstate)
* _bt_findinsertloc() to reuse most of the binary search work we do
* here.
*
- * Do not call here when there are NULL values in scan key. NULL should be
- * considered unequal to NULL when checking for duplicates, but we are not
- * prepared to handle that correctly.
+ * This code treats NULLs as equal, unlike the default semantics for unique
+ * indexes. So do not call here when there are NULL values in scan key and
+ * the index uses the default NULLS DISTINCT mode.
*/
static TransactionId
_bt_check_unique(Relation rel, BTInsertState insertstate, Relation heapRel,
@@ -443,7 +443,7 @@ _bt_check_unique(Relation rel, BTInsertState insertstate, Relation heapRel,
* Scan over all equal tuples, looking for live conflicts.
*/
Assert(!insertstate->bounds_valid || insertstate->low == offset);
- Assert(!itup_key->anynullkeys);
+ Assert(!itup_key->anynullkeys || rel->rd_index->indnullsnotdistinct);
Assert(itup_key->scantid == NULL);
for (;;)
{
diff --git a/src/backend/access/nbtree/nbtsort.c b/src/backend/access/nbtree/nbtsort.c
index 54c8eb1289..0c012da24d 100644
--- a/src/backend/access/nbtree/nbtsort.c
+++ b/src/backend/access/nbtree/nbtsort.c
@@ -89,6 +89,7 @@ typedef struct BTSpool
Relation heap;
Relation index;
bool isunique;
+ bool nulls_not_distinct;
} BTSpool;
/*
@@ -106,6 +107,7 @@ typedef struct BTShared
Oid heaprelid;
Oid indexrelid;
bool isunique;
+ bool nulls_not_distinct;
bool isconcurrent;
int scantuplesortstates;
@@ -206,6 +208,7 @@ typedef struct BTLeader
typedef struct BTBuildState
{
bool isunique;
+ bool nulls_not_distinct;
bool havedead;
Relation heap;
BTSpool *spool;
@@ -307,6 +310,7 @@ btbuild(Relation heap, Relation index, IndexInfo *indexInfo)
#endif /* BTREE_BUILD_STATS */
buildstate.isunique = indexInfo->ii_Unique;
+ buildstate.nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
buildstate.havedead = false;
buildstate.heap = heap;
buildstate.spool = NULL;
@@ -380,6 +384,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
btspool->heap = heap;
btspool->index = index;
btspool->isunique = indexInfo->ii_Unique;
+ btspool->nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
/* Save as primary spool */
buildstate->spool = btspool;
@@ -429,6 +434,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
*/
buildstate->spool->sortstate =
tuplesort_begin_index_btree(heap, index, buildstate->isunique,
+ buildstate->nulls_not_distinct,
maintenance_work_mem, coordinate,
false);
@@ -468,7 +474,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
* full, so we give it only work_mem
*/
buildstate->spool2->sortstate =
- tuplesort_begin_index_btree(heap, index, false, work_mem,
+ tuplesort_begin_index_btree(heap, index, false, false, work_mem,
coordinate2, false);
}
@@ -1554,6 +1560,7 @@ _bt_begin_parallel(BTBuildState *buildstate, bool isconcurrent, int request)
btshared->heaprelid = RelationGetRelid(btspool->heap);
btshared->indexrelid = RelationGetRelid(btspool->index);
btshared->isunique = btspool->isunique;
+ btshared->nulls_not_distinct = btspool->nulls_not_distinct;
btshared->isconcurrent = isconcurrent;
btshared->scantuplesortstates = scantuplesortstates;
ConditionVariableInit(&btshared->workersdonecv);
@@ -1747,6 +1754,7 @@ _bt_leader_participate_as_worker(BTBuildState *buildstate)
leaderworker->heap = buildstate->spool->heap;
leaderworker->index = buildstate->spool->index;
leaderworker->isunique = buildstate->spool->isunique;
+ leaderworker->nulls_not_distinct = buildstate->spool->nulls_not_distinct;
/* Initialize second spool, if required */
if (!btleader->btshared->isunique)
@@ -1839,6 +1847,7 @@ _bt_parallel_build_main(dsm_segment *seg, shm_toc *toc)
btspool->heap = heapRel;
btspool->index = indexRel;
btspool->isunique = btshared->isunique;
+ btspool->nulls_not_distinct = btshared->nulls_not_distinct;
/* Look up shared state private to tuplesort.c */
sharedsort = shm_toc_lookup(toc, PARALLEL_KEY_TUPLESORT, false);
@@ -1921,6 +1930,7 @@ _bt_parallel_scan_and_sort(BTSpool *btspool, BTSpool *btspool2,
btspool->sortstate = tuplesort_begin_index_btree(btspool->heap,
btspool->index,
btspool->isunique,
+ btspool->nulls_not_distinct,
sortmem, coordinate,
false);
@@ -1943,13 +1953,14 @@ _bt_parallel_scan_and_sort(BTSpool *btspool, BTSpool *btspool2,
coordinate2->nParticipants = -1;
coordinate2->sharedsort = sharedsort2;
btspool2->sortstate =
- tuplesort_begin_index_btree(btspool->heap, btspool->index, false,
+ tuplesort_begin_index_btree(btspool->heap, btspool->index, false, false,
Min(sortmem, work_mem), coordinate2,
false);
}
/* Fill in buildstate for _bt_build_callback() */
buildstate.isunique = btshared->isunique;
+ buildstate.nulls_not_distinct = btshared->nulls_not_distinct;
buildstate.havedead = false;
buildstate.heap = btspool->heap;
buildstate.spool = btspool;
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 26bfa74ce7..c73d3d33a7 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -612,6 +612,7 @@ UpdateIndexRelation(Oid indexoid,
values[Anum_pg_index_indnatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexAttrs);
values[Anum_pg_index_indnkeyatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexKeyAttrs);
values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique);
+ values[Anum_pg_index_indnullsnotdistinct - 1] = BoolGetDatum(indexInfo->ii_NullsNotDistinct);
values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary);
values[Anum_pg_index_indisexclusion - 1] = BoolGetDatum(isexclusion);
values[Anum_pg_index_indimmediate - 1] = BoolGetDatum(immediate);
@@ -1348,6 +1349,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
indexExprs,
indexPreds,
oldInfo->ii_Unique,
+ oldInfo->ii_NullsNotDistinct,
false, /* not ready for inserts */
true);
@@ -2398,6 +2400,7 @@ BuildIndexInfo(Relation index)
RelationGetIndexExpressions(index),
RelationGetIndexPredicate(index),
indexStruct->indisunique,
+ indexStruct->indnullsnotdistinct,
indexStruct->indisready,
false);
@@ -2457,6 +2460,7 @@ BuildDummyIndexInfo(Relation index)
RelationGetDummyIndexExpressions(index),
NIL,
indexStruct->indisunique,
+ indexStruct->indnullsnotdistinct,
indexStruct->indisready,
false);
@@ -2490,6 +2494,9 @@ CompareIndexInfo(IndexInfo *info1, IndexInfo *info2,
if (info1->ii_Unique != info2->ii_Unique)
return false;
+ if (info1->ii_NullsNotDistinct != info2->ii_NullsNotDistinct)
+ return false;
+
/* indexes are only equivalent if they have the same access method */
if (info1->ii_Am != info2->ii_Am)
return false;
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 11d9dd60c2..caafb00c44 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1838,7 +1838,11 @@ CREATE VIEW table_constraints AS
AS is_deferrable,
CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
AS initially_deferred,
- CAST('YES' AS yes_or_no) AS enforced
+ CAST('YES' AS yes_or_no) AS enforced,
+ CAST(CASE WHEN c.contype = 'u'
+ THEN CASE WHEN (SELECT NOT indnullsnotdistinct FROM pg_index WHERE indexrelid = conindid) THEN 'YES' ELSE 'NO' END
+ END
+ AS yes_or_no) AS nulls_distinct
FROM pg_namespace nc,
pg_namespace nr,
@@ -1868,7 +1872,8 @@ CREATE VIEW table_constraints AS
CAST('CHECK' AS character_data) AS constraint_type,
CAST('NO' AS yes_or_no) AS is_deferrable,
CAST('NO' AS yes_or_no) AS initially_deferred,
- CAST('YES' AS yes_or_no) AS enforced
+ CAST('YES' AS yes_or_no) AS enforced,
+ CAST(NULL AS yes_or_no) AS nulls_distinct
FROM pg_namespace nr,
pg_class r,
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 9f424216e2..accac5315f 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -228,6 +228,7 @@ F263 Comma-separated predicates in simple CASE expression NO
F271 Compound character literals YES
F281 LIKE enhancements YES
F291 UNIQUE predicate NO
+F292 UNIQUE null treatment YES SQL:202x draft
F301 CORRESPONDING in query expressions NO
F302 INTERSECT table operator YES
F302 INTERSECT table operator 01 INTERSECT DISTINCT table operator YES
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 0db90c2011..9614f64b92 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -301,6 +301,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
indexInfo->ii_ExclusionStrats = NULL;
indexInfo->ii_OpclassOptions = NULL;
indexInfo->ii_Unique = true;
+ indexInfo->ii_NullsNotDistinct = false;
indexInfo->ii_ReadyForInserts = true;
indexInfo->ii_Concurrent = false;
indexInfo->ii_BrokenHotChain = false;
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index c14ca27c5e..ab48a40a20 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -226,7 +226,7 @@ CheckIndexCompatible(Oid oldId,
* ii_NumIndexKeyAttrs with same value.
*/
indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes,
- accessMethodId, NIL, NIL, false, false, false);
+ accessMethodId, NIL, NIL, false, false, false, false);
typeObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
collationObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
@@ -867,6 +867,7 @@ DefineIndex(Oid relationId,
NIL, /* expressions, NIL for now */
make_ands_implicit((Expr *) stmt->whereClause),
stmt->unique,
+ stmt->nulls_not_distinct,
!concurrent,
concurrent);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 38251c2b8e..361b60b02f 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3056,6 +3056,7 @@ _copyConstraint(const Constraint *from)
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
COPY_SCALAR_FIELD(generated_when);
+ COPY_SCALAR_FIELD(nulls_not_distinct);
COPY_NODE_FIELD(keys);
COPY_NODE_FIELD(including);
COPY_NODE_FIELD(exclusions);
@@ -3647,6 +3648,7 @@ _copyIndexStmt(const IndexStmt *from)
COPY_SCALAR_FIELD(oldCreateSubid);
COPY_SCALAR_FIELD(oldFirstRelfilenodeSubid);
COPY_SCALAR_FIELD(unique);
+ COPY_SCALAR_FIELD(nulls_not_distinct);
COPY_SCALAR_FIELD(primary);
COPY_SCALAR_FIELD(isconstraint);
COPY_SCALAR_FIELD(deferrable);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 8a1762000c..88268aec0e 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1393,6 +1393,7 @@ _equalIndexStmt(const IndexStmt *a, const IndexStmt *b)
COMPARE_SCALAR_FIELD(oldCreateSubid);
COMPARE_SCALAR_FIELD(oldFirstRelfilenodeSubid);
COMPARE_SCALAR_FIELD(unique);
+ COMPARE_SCALAR_FIELD(nulls_not_distinct);
COMPARE_SCALAR_FIELD(primary);
COMPARE_SCALAR_FIELD(isconstraint);
COMPARE_SCALAR_FIELD(deferrable);
@@ -2677,6 +2678,7 @@ _equalConstraint(const Constraint *a, const Constraint *b)
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
COMPARE_SCALAR_FIELD(generated_when);
+ COMPARE_SCALAR_FIELD(nulls_not_distinct);
COMPARE_NODE_FIELD(keys);
COMPARE_NODE_FIELD(including);
COMPARE_NODE_FIELD(exclusions);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 01c110cd2f..52aaf0b7a5 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -741,7 +741,7 @@ make_ands_implicit(Expr *clause)
*/
IndexInfo *
makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
- List *predicates, bool unique, bool isready, bool concurrent)
+ List *predicates, bool unique, bool nulls_not_distinct, bool isready, bool concurrent)
{
IndexInfo *n = makeNode(IndexInfo);
@@ -750,6 +750,7 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
Assert(n->ii_NumIndexKeyAttrs != 0);
Assert(n->ii_NumIndexKeyAttrs <= n->ii_NumIndexAttrs);
n->ii_Unique = unique;
+ n->ii_NullsNotDistinct = nulls_not_distinct;
n->ii_ReadyForInserts = isready;
n->ii_Concurrent = concurrent;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 87561cbb6f..cd1feb7b57 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2770,6 +2770,7 @@ _outIndexStmt(StringInfo str, const IndexStmt *node)
WRITE_UINT_FIELD(oldCreateSubid);
WRITE_UINT_FIELD(oldFirstRelfilenodeSubid);
WRITE_BOOL_FIELD(unique);
+ WRITE_BOOL_FIELD(nulls_not_distinct);
WRITE_BOOL_FIELD(primary);
WRITE_BOOL_FIELD(isconstraint);
WRITE_BOOL_FIELD(deferrable);
@@ -3702,6 +3703,7 @@ _outConstraint(StringInfo str, const Constraint *node)
case CONSTR_UNIQUE:
appendStringInfoString(str, "UNIQUE");
+ WRITE_BOOL_FIELD(nulls_not_distinct);
WRITE_NODE_FIELD(keys);
WRITE_NODE_FIELD(including);
WRITE_NODE_FIELD(options);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 39a2849eba..ea7cd03cd2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -602,6 +602,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
+%type <boolean> opt_unique_null_treatment
%type <ival> generated_when override_kind
%type <partspec> PartitionSpec OptPartitionSpec
%type <partelem> part_elem
@@ -3600,15 +3601,16 @@ ColConstraintElem:
n->location = @1;
$$ = (Node *)n;
}
- | UNIQUE opt_definition OptConsTableSpace
+ | UNIQUE opt_unique_null_treatment opt_definition OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
n->location = @1;
+ n->nulls_not_distinct = !$2;
n->keys = NULL;
- n->options = $2;
+ n->options = $3;
n->indexname = NULL;
- n->indexspace = $3;
+ n->indexspace = $4;
$$ = (Node *)n;
}
| PRIMARY KEY opt_definition OptConsTableSpace
@@ -3692,6 +3694,12 @@ ColConstraintElem:
}
;
+opt_unique_null_treatment:
+ NULLS_P DISTINCT { $$ = true; }
+ | NULLS_P NOT DISTINCT { $$ = false; }
+ | /*EMPTY*/ { $$ = true; }
+ ;
+
generated_when:
ALWAYS { $$ = ATTRIBUTE_IDENTITY_ALWAYS; }
| BY DEFAULT { $$ = ATTRIBUTE_IDENTITY_BY_DEFAULT; }
@@ -3804,18 +3812,19 @@ ConstraintElem:
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
}
- | UNIQUE '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
+ | UNIQUE opt_unique_null_treatment '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
ConstraintAttributeSpec
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
n->location = @1;
- n->keys = $3;
- n->including = $5;
- n->options = $6;
+ n->nulls_not_distinct = !$2;
+ n->keys = $4;
+ n->including = $6;
+ n->options = $7;
n->indexname = NULL;
- n->indexspace = $7;
- processCASbits($8, @8, "UNIQUE",
+ n->indexspace = $8;
+ processCASbits($9, @9, "UNIQUE",
&n->deferrable, &n->initdeferred, NULL,
NULL, yyscanner);
$$ = (Node *)n;
@@ -7316,7 +7325,7 @@ defacl_privilege_target:
IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
@@ -7326,9 +7335,10 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->accessMethod = $8;
n->indexParams = $10;
n->indexIncludingParams = $12;
- n->options = $13;
- n->tableSpace = $14;
- n->whereClause = $15;
+ n->nulls_not_distinct = !$13;
+ n->options = $14;
+ n->tableSpace = $15;
+ n->whereClause = $16;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -7346,7 +7356,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
}
| CREATE opt_unique INDEX opt_concurrently IF_P NOT EXISTS name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
@@ -7356,9 +7366,10 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->accessMethod = $11;
n->indexParams = $13;
n->indexIncludingParams = $15;
- n->options = $16;
- n->tableSpace = $17;
- n->whereClause = $18;
+ n->nulls_not_distinct = !$16;
+ n->options = $17;
+ n->tableSpace = $18;
+ n->whereClause = $19;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -13621,7 +13632,7 @@ a_expr: c_expr { $$ = $1; }
else
$$ = (Node *) makeA_Expr(AEXPR_OP_ALL, $2, $1, $5, @2);
}
- | UNIQUE select_with_parens
+ | UNIQUE opt_unique_null_treatment select_with_parens
{
/* Not sure how to get rid of the parentheses
* but there are lots of shift/reduce errors without them.
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e5eefdbd43..5c646fd621 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;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 4df8cc5abf..c75e630fcc 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1440,6 +1440,9 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
{
appendStringInfoChar(&buf, ')');
+ if (idxrec->indnullsnotdistinct)
+ appendStringInfo(&buf, " NULLS NOT DISTINCT");
+
/*
* If it has options, append "WITH (options)"
*/
@@ -2298,9 +2301,20 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
/* Start off the constraint definition */
if (conForm->contype == CONSTRAINT_PRIMARY)
- appendStringInfoString(&buf, "PRIMARY KEY (");
+ appendStringInfoString(&buf, "PRIMARY KEY ");
else
- appendStringInfoString(&buf, "UNIQUE (");
+ appendStringInfoString(&buf, "UNIQUE ");
+
+ indexId = conForm->conindid;
+
+ indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId));
+ if (!HeapTupleIsValid(indtup))
+ elog(ERROR, "cache lookup failed for index %u", indexId);
+ if (conForm->contype == CONSTRAINT_UNIQUE &&
+ ((Form_pg_index) GETSTRUCT(indtup))->indnullsnotdistinct)
+ appendStringInfoString(&buf, "NULLS NOT DISTINCT ");
+
+ appendStringInfoString(&buf, "(");
/* Fetch and build target column list */
val = SysCacheGetAttr(CONSTROID, tup,
@@ -2313,12 +2327,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
appendStringInfoChar(&buf, ')');
- indexId = conForm->conindid;
-
/* Build including column list (from pg_index.indkeys) */
- indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId));
- if (!HeapTupleIsValid(indtup))
- elog(ERROR, "cache lookup failed for index %u", indexId);
val = SysCacheGetAttr(INDEXRELID, indtup,
Anum_pg_index_indnatts, &isnull);
if (isnull)
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 13d9994af3..615478c31d 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2237,6 +2237,7 @@ RelationReloadIndexInfo(Relation relation)
* the array fields are allowed to change, though.
*/
relation->rd_index->indisunique = index->indisunique;
+ relation->rd_index->indnullsnotdistinct = index->indnullsnotdistinct;
relation->rd_index->indisprimary = index->indisprimary;
relation->rd_index->indisexclusion = index->indisexclusion;
relation->rd_index->indimmediate = index->indimmediate;
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index b17347b214..d874f9b75d 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -460,6 +460,7 @@ struct Tuplesortstate
/* These are specific to the index_btree subcase: */
bool enforceUnique; /* complain if we find duplicate tuples */
+ bool uniqueNullsNotDistinct; /* unique constraint null treatment */
/* These are specific to the index_hash subcase: */
uint32 high_mask; /* masks for sortable part of hash code */
@@ -1066,6 +1067,7 @@ Tuplesortstate *
tuplesort_begin_index_btree(Relation heapRel,
Relation indexRel,
bool enforceUnique,
+ bool uniqueNullsNotDistinct,
int workMem,
SortCoordinate coordinate,
bool randomAccess)
@@ -1104,6 +1106,7 @@ tuplesort_begin_index_btree(Relation heapRel,
state->heapRel = heapRel;
state->indexRel = indexRel;
state->enforceUnique = enforceUnique;
+ state->uniqueNullsNotDistinct = uniqueNullsNotDistinct;
indexScanKey = _bt_mkscankey(indexRel, NULL);
@@ -4267,14 +4270,15 @@ comparetup_index_btree(const SortTuple *a, const SortTuple *b,
/*
* If btree has asked us to enforce uniqueness, complain if two equal
- * tuples are detected (unless there was at least one NULL field).
+ * tuples are detected (unless there was at least one NULL field and NULLS
+ * NOT DISTINCT was not set).
*
* It is sufficient to make the test here, because if two tuples are equal
* they *must* get compared at some stage of the sort --- otherwise the
* sort algorithm wouldn't have checked whether one must appear before the
* other.
*/
- if (state->enforceUnique && !equal_hasnull)
+ if (state->enforceUnique && !(!state->uniqueNullsNotDistinct && equal_hasnull))
{
Datum values[INDEX_MAX_KEYS];
bool isnull[INDEX_MAX_KEYS];
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 90ac445bcd..cbed55bee8 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -7180,6 +7180,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_indkey,
i_indisclustered,
i_indisreplident,
+ i_indnullsnotdistinct,
i_contype,
i_conname,
i_condeferrable,
@@ -7237,6 +7238,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"i.indnatts AS indnatts, "
"i.indkey, i.indisclustered, "
"i.indisreplident, "
+ "i.indnullsnotdistinct, " // TODO: make version-specific
"c.contype, c.conname, "
"c.condeferrable, c.condeferred, "
"c.tableoid AS contableoid, "
@@ -7419,6 +7421,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_indkey = PQfnumber(res, "indkey");
i_indisclustered = PQfnumber(res, "indisclustered");
i_indisreplident = PQfnumber(res, "indisreplident");
+ i_indnullsnotdistinct = PQfnumber(res, "indnullsnotdistinct");
i_contype = PQfnumber(res, "contype");
i_conname = PQfnumber(res, "conname");
i_condeferrable = PQfnumber(res, "condeferrable");
@@ -7460,6 +7463,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
indxinfo[j].indkeys, indxinfo[j].indnattrs);
indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
indxinfo[j].indisreplident = (PQgetvalue(res, j, i_indisreplident)[0] == 't');
+ indxinfo[j].indnullsnotdistinct = (PQgetvalue(res, j, i_indnullsnotdistinct)[0] == 't');
indxinfo[j].parentidx = atooid(PQgetvalue(res, j, i_parentidx));
indxinfo[j].partattaches = (SimplePtrList)
{
@@ -17069,8 +17073,11 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
}
else
{
- appendPQExpBuffer(q, "%s (",
+ appendPQExpBuffer(q, "%s",
coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
+ if (indxinfo->indnullsnotdistinct)
+ appendPQExpBuffer(q, " NULLS NOT DISTINCT");
+ appendPQExpBuffer(q, " (");
for (k = 0; k < indxinfo->indnkeyattrs; k++)
{
int indkey = (int) indxinfo->indkeys[k];
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index f5e170e0db..25795a8df4 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -380,6 +380,7 @@ typedef struct _indxInfo
* contains both key and nonkey attributes */
bool indisclustered;
bool indisreplident;
+ bool indnullsnotdistinct;
Oid parentidx; /* if a partition, parent index OID */
SimplePtrList partattaches; /* if partitioned, partition attach objects */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 8333558bda..e6d07492c4 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2438,6 +2438,11 @@ describeOneTableDetails(const char *schemaname,
else
appendPQExpBufferStr(&buf, "false AS indisreplident,\n");
+ if (pset.sversion >= 150000)
+ appendPQExpBufferStr(&buf, "i.indnullsnotdistinct,\n");
+ else
+ appendPQExpBufferStr(&buf, "false AS indnullsnotdistinct,\n");
+
appendPQExpBuffer(&buf, " a.amname, c2.relname, "
"pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
"FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
@@ -2462,14 +2467,20 @@ describeOneTableDetails(const char *schemaname,
char *deferrable = PQgetvalue(result, 0, 4);
char *deferred = PQgetvalue(result, 0, 5);
char *indisreplident = PQgetvalue(result, 0, 6);
- char *indamname = PQgetvalue(result, 0, 7);
- char *indtable = PQgetvalue(result, 0, 8);
- char *indpred = PQgetvalue(result, 0, 9);
+ char *indnullsnotdistinct = PQgetvalue(result, 0, 7);
+ char *indamname = PQgetvalue(result, 0, 8);
+ char *indtable = PQgetvalue(result, 0, 9);
+ char *indpred = PQgetvalue(result, 0, 10);
if (strcmp(indisprimary, "t") == 0)
printfPQExpBuffer(&tmpbuf, _("primary key, "));
else if (strcmp(indisunique, "t") == 0)
- printfPQExpBuffer(&tmpbuf, _("unique, "));
+ {
+ printfPQExpBuffer(&tmpbuf, _("unique"));
+ if (strcmp(indnullsnotdistinct, "t") == 0)
+ appendPQExpBufferStr(&tmpbuf, _(" nulls not distinct"));
+ appendPQExpBuffer(&tmpbuf, _(", "));
+ }
else
resetPQExpBuffer(&tmpbuf);
appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
@@ -2544,6 +2555,10 @@ describeOneTableDetails(const char *schemaname,
appendPQExpBufferStr(&buf, ", false AS indisreplident");
if (pset.sversion >= 80000)
appendPQExpBufferStr(&buf, ", c2.reltablespace");
+ if (pset.sversion >= 150000)
+ appendPQExpBufferStr(&buf, ", i.indnullsnotdistinct");
+ else
+ appendPQExpBufferStr(&buf, ", false AS indnullsnotdistinct");
appendPQExpBufferStr(&buf,
"\nFROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i\n");
if (pset.sversion >= 90000)
@@ -2585,9 +2600,12 @@ describeOneTableDetails(const char *schemaname,
else if (strcmp(PQgetvalue(result, i, 2), "t") == 0)
{
if (strcmp(PQgetvalue(result, i, 7), "u") == 0)
- appendPQExpBufferStr(&buf, " UNIQUE CONSTRAINT,");
+ appendPQExpBufferStr(&buf, " UNIQUE CONSTRAINT");
else
- appendPQExpBufferStr(&buf, " UNIQUE,");
+ appendPQExpBufferStr(&buf, " UNIQUE");
+ if (strcmp(PQgetvalue(result, i, 12), "t") == 0)
+ appendPQExpBufferStr(&buf, " NULLS NOT DISTINCT");
+ appendPQExpBufferStr(&buf, ",");
}
/* Everything after "USING" is echoed verbatim */
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index a5a6075d4d..8c8300284e 100644
--- a/src/include/catalog/pg_index.h
+++ b/src/include/catalog/pg_index.h
@@ -34,6 +34,7 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO
int16 indnatts; /* total number of columns in index */
int16 indnkeyatts; /* number of key columns in index */
bool indisunique; /* is this a unique index? */
+ bool indnullsnotdistinct; /* null treatment in unique index */
bool indisprimary; /* is this index for primary key? */
bool indisexclusion; /* is this index for exclusion constraint? */
bool indimmediate; /* is uniqueness enforced immediately? */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 37cb4f3d59..c7765cc8e7 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -170,6 +170,7 @@ typedef struct IndexInfo
uint16 *ii_UniqueStrats; /* array with one entry per column */
Datum *ii_OpclassOptions; /* array with one entry per column */
bool ii_Unique;
+ bool ii_NullsNotDistinct;
bool ii_ReadyForInserts;
bool ii_Concurrent;
bool ii_BrokenHotChain;
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index 48a7ebfe45..aad5d141c3 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -96,7 +96,7 @@ extern List *make_ands_implicit(Expr *clause);
extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid,
List *expressions, List *predicates,
- bool unique, bool isready, bool concurrent);
+ bool unique, bool nulls_not_distinct, bool isready, bool concurrent);
extern DefElem *makeDefElem(char *name, Node *arg, int location);
extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 7af13dee43..cdf285aa93 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2263,6 +2263,7 @@ typedef struct Constraint
char generated_when; /* ALWAYS or BY DEFAULT */
/* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */
+ bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
List *keys; /* String nodes naming referenced key
* column(s) */
List *including; /* String nodes naming referenced nonkey
@@ -2890,6 +2891,7 @@ typedef struct IndexStmt
SubTransactionId oldFirstRelfilenodeSubid; /* rd_firstRelfilenodeSubid of
* oldNode */
bool unique; /* is index unique? */
+ bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
bool primary; /* is index a primary key? */
bool isconstraint; /* is it for a pkey/unique constraint? */
bool deferrable; /* is the constraint DEFERRABLE? */
diff --git a/src/include/utils/tuplesort.h b/src/include/utils/tuplesort.h
index f94949370b..b30ca09598 100644
--- a/src/include/utils/tuplesort.h
+++ b/src/include/utils/tuplesort.h
@@ -208,6 +208,7 @@ extern Tuplesortstate *tuplesort_begin_cluster(TupleDesc tupDesc,
extern Tuplesortstate *tuplesort_begin_index_btree(Relation heapRel,
Relation indexRel,
bool enforceUnique,
+ bool uniqueNullsNotDistinct,
int workMem, SortCoordinate coordinate,
bool randomAccess);
extern Tuplesortstate *tuplesort_begin_index_hash(Relation heapRel,
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index 4750eac359..308818b7e1 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1272,6 +1272,57 @@ SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
DROP INDEX hash_tuplesort_idx;
RESET maintenance_work_mem;
--
+-- Test unique null behavior
+--
+CREATE TABLE unique_tbl (i int, t text);
+CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
+CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;
+INSERT INTO unique_tbl VALUES (1, 'one');
+INSERT INTO unique_tbl VALUES (2, 'two');
+INSERT INTO unique_tbl VALUES (3, 'three');
+INSERT INTO unique_tbl VALUES (4, 'four');
+INSERT INTO unique_tbl VALUES (5, 'one');
+INSERT INTO unique_tbl (t) VALUES ('six');
+INSERT INTO unique_tbl (t) VALUES ('seven'); -- error from unique_idx2
+ERROR: duplicate key value violates unique constraint "unique_idx2"
+DETAIL: Key (i)=(null) already exists.
+DROP INDEX unique_idx1, unique_idx2;
+INSERT INTO unique_tbl (t) VALUES ('seven');
+-- build indexes on filled table
+CREATE UNIQUE INDEX unique_idx3 ON unique_tbl (i) NULLS DISTINCT; -- ok
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- error
+ERROR: could not create unique index "unique_idx4"
+DETAIL: Key (i)=(null) is duplicated.
+DELETE FROM unique_tbl WHERE t = 'seven';
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- ok now
+\d unique_idx3
+ Index "public.unique_idx3"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ i | integer | yes | i
+unique, btree, for table "public.unique_tbl"
+
+\d unique_idx4
+ Index "public.unique_idx4"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ i | integer | yes | i
+unique nulls not distinct, btree, for table "public.unique_tbl"
+
+SELECT pg_get_indexdef('unique_idx3'::regclass);
+ pg_get_indexdef
+----------------------------------------------------------------------
+ CREATE UNIQUE INDEX unique_idx3 ON public.unique_tbl USING btree (i)
+(1 row)
+
+SELECT pg_get_indexdef('unique_idx4'::regclass);
+ pg_get_indexdef
+-----------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX unique_idx4 ON public.unique_tbl USING btree (i) NULLS NOT DISTINCT
+(1 row)
+
+DROP TABLE unique_tbl;
+--
-- Test functional index
--
CREATE TABLE func_index_heap (f1 text, f2 text);
diff --git a/src/test/regress/input/constraints.source b/src/test/regress/input/constraints.source
index 6bb7648321..2dae540cfc 100644
--- a/src/test/regress/input/constraints.source
+++ b/src/test/regress/input/constraints.source
@@ -301,6 +301,20 @@ SELECT * FROM UNIQUE_TBL;
DROP TABLE UNIQUE_TBL;
+CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
+
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+INSERT INTO UNIQUE_TBL VALUES (2, 'two');
+INSERT INTO UNIQUE_TBL VALUES (1, 'three');
+INSERT INTO UNIQUE_TBL VALUES (4, 'four');
+INSERT INTO UNIQUE_TBL VALUES (5, 'one');
+INSERT INTO UNIQUE_TBL (t) VALUES ('six');
+INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
+
+SELECT * FROM UNIQUE_TBL;
+
+DROP TABLE UNIQUE_TBL;
+
CREATE TABLE UNIQUE_TBL (i int, t text,
UNIQUE(i,t));
diff --git a/src/test/regress/output/constraints.source b/src/test/regress/output/constraints.source
index eff793cc3d..266baa985c 100644
--- a/src/test/regress/output/constraints.source
+++ b/src/test/regress/output/constraints.source
@@ -441,6 +441,29 @@ SELECT * FROM UNIQUE_TBL;
6 | six-upsert-insert
(7 rows)
+DROP TABLE UNIQUE_TBL;
+CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+INSERT INTO UNIQUE_TBL VALUES (2, 'two');
+INSERT INTO UNIQUE_TBL VALUES (1, 'three');
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(1) already exists.
+INSERT INTO UNIQUE_TBL VALUES (4, 'four');
+INSERT INTO UNIQUE_TBL VALUES (5, 'one');
+INSERT INTO UNIQUE_TBL (t) VALUES ('six');
+INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(null) already exists.
+SELECT * FROM UNIQUE_TBL;
+ i | t
+---+------
+ 1 | one
+ 2 | two
+ 4 | four
+ 5 | one
+ | six
+(5 rows)
+
DROP TABLE UNIQUE_TBL;
CREATE TABLE UNIQUE_TBL (i int, t text,
UNIQUE(i,t));
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 22209b0691..49b4d0caa9 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -387,6 +387,42 @@ CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fi
RESET maintenance_work_mem;
+--
+-- Test unique null behavior
+--
+CREATE TABLE unique_tbl (i int, t text);
+
+CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
+CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;
+
+INSERT INTO unique_tbl VALUES (1, 'one');
+INSERT INTO unique_tbl VALUES (2, 'two');
+INSERT INTO unique_tbl VALUES (3, 'three');
+INSERT INTO unique_tbl VALUES (4, 'four');
+INSERT INTO unique_tbl VALUES (5, 'one');
+INSERT INTO unique_tbl (t) VALUES ('six');
+INSERT INTO unique_tbl (t) VALUES ('seven'); -- error from unique_idx2
+
+DROP INDEX unique_idx1, unique_idx2;
+
+INSERT INTO unique_tbl (t) VALUES ('seven');
+
+-- build indexes on filled table
+CREATE UNIQUE INDEX unique_idx3 ON unique_tbl (i) NULLS DISTINCT; -- ok
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- error
+
+DELETE FROM unique_tbl WHERE t = 'seven';
+
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- ok now
+
+\d unique_idx3
+\d unique_idx4
+SELECT pg_get_indexdef('unique_idx3'::regclass);
+SELECT pg_get_indexdef('unique_idx4'::regclass);
+
+DROP TABLE unique_tbl;
+
+
--
-- Test functional index
--
--
2.33.0
Re: UNIQUE null treatment option
On Fri, Aug 27, 2021 at 3:38 PM Peter Eisentraut <
peter.eisentraut@enterprisedb.com> wrote:
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.The CREATE UNIQUE INDEX syntax extension is not from the standard,
it's my own invention.
For the unique index syntax, should this be selectable per
column/expression, rather than for the entire index as a whole?
.m
Re: UNIQUE null treatment option
On 27.08.21 14:44, Marko Tiikkaja wrote:
On Fri, Aug 27, 2021 at 3:38 PM Peter Eisentraut
<peter.eisentraut@enterprisedb.com
<mailto:peter.eisentraut@enterprisedb.com>> wrote: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.The CREATE UNIQUE INDEX syntax extension is not from the standard,
it's my own invention.For the unique index syntax, should this be selectable per
column/expression, rather than for the entire index as a whole?
Semantically, this would be possible, but the bookkeeping to make it
work seems out of proportion with the utility. And you'd have the
unique index syntax out of sync with the unique constraint syntax, which
would be pretty confusing.
Re: UNIQUE null treatment option
Here is a rebased version of this patch.
Show quoted text
On 27.08.21 14:38, Peter Eisentraut wrote:
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 apparently 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. But perhaps the double
negatives make some code harder to read.)
Attachments:
v2-0001-Add-UNIQUE-null-treatment-option.patchtext/plain; charset=UTF-8; name=v2-0001-Add-UNIQUE-null-treatment-option.patchDownload
From ffd6c8e0ce24f3c56bd44e71588d4165e20e9157 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Wed, 29 Dec 2021 10:49:57 +0100
Subject: [PATCH v2] 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.
XXX 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. But perhaps the double
negatives make some code harder to read.
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 ++--
src/backend/access/nbtree/nbtinsert.c | 10 ++--
src/backend/access/nbtree/nbtsort.c | 15 +++++-
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/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 +++++++++++++
34 files changed, 342 insertions(+), 58 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 03e2537b07..f0d49e4841 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4275,6 +4275,19 @@ <title><structname>pg_index</structname> Columns</title>
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>indnullsnotdistinct</structfield> <type>bool</type>
+ </para>
+ <para>
+ 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).
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>indisprimary</structfield> <type>bool</type>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 64d9030652..f622285ba0 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -759,14 +759,33 @@ <title>Unique Constraints</title>
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 <literal>NULLS
+ NOT DISTINCT</literal>, like
+<programlisting>
+CREATE TABLE products (
+ product_no integer UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis>,
+ name text,
+ price numeric
+);
+</programlisting>
+ or
+<programlisting>
+CREATE TABLE products (
+ product_no integer,
+ name text,
+ price numeric,
+ UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis> (product_no)
+);
+</programlisting>
+ The default behavior can be specified explicitly using <literal>NULLS
+ DISTINCT</literal>. 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.
</para>
</sect2>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index c5e68c175f..350c75bc31 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -6899,6 +6899,18 @@ <title><structname>table_constraints</structname> Columns</title>
<literal>YES</literal>)
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>nulls_distinct</structfield> <type>yes_or_no</type>
+ </para>
+ <para>
+ If the constraint is a unique constraint, then <literal>YES</literal>
+ if the constraint treats nulls as distinct or <literal>NO</literal> if
+ it treats nulls as not distinct, otherwise null for other types of
+ constraints.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index a76e2e7322..b20f6c651c 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -103,7 +103,7 @@
DEFAULT <replaceable>default_expr</replaceable> |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
- UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
@@ -113,7 +113,7 @@
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 89a4d746f6..91eaaabc90 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -24,6 +24,7 @@
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> [ ( <replaceable class="parameter">opclass_parameter</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+ [ NULLS [ NOT ] DISTINCT ]
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
@@ -334,6 +335,18 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>NULLS DISTINCT</literal></term>
+ <term><literal>NULLS NOT DISTINCT</literal></term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">storage_parameter</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index b97bb9ded1..7e4ef312c0 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -67,7 +67,7 @@
DEFAULT <replaceable>default_expr</replaceable> |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
- UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
@@ -77,7 +77,7 @@
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
@@ -917,8 +917,8 @@ <title>Parameters</title>
</varlistentry>
<varlistentry>
- <term><literal>UNIQUE</literal> (column constraint)</term>
- <term><literal>UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
+ <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ]</literal> (column constraint)</term>
+ <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
<optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
<listitem>
@@ -934,7 +934,8 @@ <title>Parameters</title>
<para>
For the purpose of a unique constraint, null values are not
- considered equal.
+ considered equal, unless <literal>NULLS NOT DISTINCT</literal> is
+ specified.
</para>
<para>
diff --git a/src/backend/access/nbtree/nbtinsert.c b/src/backend/access/nbtree/nbtinsert.c
index 37ee0b4d6e..44640bc35d 100644
--- a/src/backend/access/nbtree/nbtinsert.c
+++ b/src/backend/access/nbtree/nbtinsert.c
@@ -112,7 +112,7 @@ _bt_doinsert(Relation rel, IndexTuple itup,
if (checkingunique)
{
- if (!itup_key->anynullkeys)
+ if (!itup_key->anynullkeys || rel->rd_index->indnullsnotdistinct)
{
/* No (heapkeyspace) scantid until uniqueness established */
itup_key->scantid = NULL;
@@ -398,9 +398,9 @@ _bt_search_insert(Relation rel, BTInsertState insertstate)
* _bt_findinsertloc() to reuse most of the binary search work we do
* here.
*
- * Do not call here when there are NULL values in scan key. NULL should be
- * considered unequal to NULL when checking for duplicates, but we are not
- * prepared to handle that correctly.
+ * This code treats NULLs as equal, unlike the default semantics for unique
+ * indexes. So do not call here when there are NULL values in scan key and
+ * the index uses the default NULLS DISTINCT mode.
*/
static TransactionId
_bt_check_unique(Relation rel, BTInsertState insertstate, Relation heapRel,
@@ -444,7 +444,7 @@ _bt_check_unique(Relation rel, BTInsertState insertstate, Relation heapRel,
* Scan over all equal tuples, looking for live conflicts.
*/
Assert(!insertstate->bounds_valid || insertstate->low == offset);
- Assert(!itup_key->anynullkeys);
+ Assert(!itup_key->anynullkeys || rel->rd_index->indnullsnotdistinct);
Assert(itup_key->scantid == NULL);
for (;;)
{
diff --git a/src/backend/access/nbtree/nbtsort.c b/src/backend/access/nbtree/nbtsort.c
index 1e02be9746..66dc201000 100644
--- a/src/backend/access/nbtree/nbtsort.c
+++ b/src/backend/access/nbtree/nbtsort.c
@@ -89,6 +89,7 @@ typedef struct BTSpool
Relation heap;
Relation index;
bool isunique;
+ bool nulls_not_distinct;
} BTSpool;
/*
@@ -106,6 +107,7 @@ typedef struct BTShared
Oid heaprelid;
Oid indexrelid;
bool isunique;
+ bool nulls_not_distinct;
bool isconcurrent;
int scantuplesortstates;
@@ -206,6 +208,7 @@ typedef struct BTLeader
typedef struct BTBuildState
{
bool isunique;
+ bool nulls_not_distinct;
bool havedead;
Relation heap;
BTSpool *spool;
@@ -307,6 +310,7 @@ btbuild(Relation heap, Relation index, IndexInfo *indexInfo)
#endif /* BTREE_BUILD_STATS */
buildstate.isunique = indexInfo->ii_Unique;
+ buildstate.nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
buildstate.havedead = false;
buildstate.heap = heap;
buildstate.spool = NULL;
@@ -380,6 +384,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
btspool->heap = heap;
btspool->index = index;
btspool->isunique = indexInfo->ii_Unique;
+ btspool->nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
/* Save as primary spool */
buildstate->spool = btspool;
@@ -429,6 +434,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
*/
buildstate->spool->sortstate =
tuplesort_begin_index_btree(heap, index, buildstate->isunique,
+ buildstate->nulls_not_distinct,
maintenance_work_mem, coordinate,
false);
@@ -468,7 +474,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
* full, so we give it only work_mem
*/
buildstate->spool2->sortstate =
- tuplesort_begin_index_btree(heap, index, false, work_mem,
+ tuplesort_begin_index_btree(heap, index, false, false, work_mem,
coordinate2, false);
}
@@ -1554,6 +1560,7 @@ _bt_begin_parallel(BTBuildState *buildstate, bool isconcurrent, int request)
btshared->heaprelid = RelationGetRelid(btspool->heap);
btshared->indexrelid = RelationGetRelid(btspool->index);
btshared->isunique = btspool->isunique;
+ btshared->nulls_not_distinct = btspool->nulls_not_distinct;
btshared->isconcurrent = isconcurrent;
btshared->scantuplesortstates = scantuplesortstates;
ConditionVariableInit(&btshared->workersdonecv);
@@ -1747,6 +1754,7 @@ _bt_leader_participate_as_worker(BTBuildState *buildstate)
leaderworker->heap = buildstate->spool->heap;
leaderworker->index = buildstate->spool->index;
leaderworker->isunique = buildstate->spool->isunique;
+ leaderworker->nulls_not_distinct = buildstate->spool->nulls_not_distinct;
/* Initialize second spool, if required */
if (!btleader->btshared->isunique)
@@ -1846,6 +1854,7 @@ _bt_parallel_build_main(dsm_segment *seg, shm_toc *toc)
btspool->heap = heapRel;
btspool->index = indexRel;
btspool->isunique = btshared->isunique;
+ btspool->nulls_not_distinct = btshared->nulls_not_distinct;
/* Look up shared state private to tuplesort.c */
sharedsort = shm_toc_lookup(toc, PARALLEL_KEY_TUPLESORT, false);
@@ -1928,6 +1937,7 @@ _bt_parallel_scan_and_sort(BTSpool *btspool, BTSpool *btspool2,
btspool->sortstate = tuplesort_begin_index_btree(btspool->heap,
btspool->index,
btspool->isunique,
+ btspool->nulls_not_distinct,
sortmem, coordinate,
false);
@@ -1950,13 +1960,14 @@ _bt_parallel_scan_and_sort(BTSpool *btspool, BTSpool *btspool2,
coordinate2->nParticipants = -1;
coordinate2->sharedsort = sharedsort2;
btspool2->sortstate =
- tuplesort_begin_index_btree(btspool->heap, btspool->index, false,
+ tuplesort_begin_index_btree(btspool->heap, btspool->index, false, false,
Min(sortmem, work_mem), coordinate2,
false);
}
/* Fill in buildstate for _bt_build_callback() */
buildstate.isunique = btshared->isunique;
+ buildstate.nulls_not_distinct = btshared->nulls_not_distinct;
buildstate.havedead = false;
buildstate.heap = btspool->heap;
buildstate.spool = btspool;
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 1757cd3446..222404c5b8 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -613,6 +613,7 @@ UpdateIndexRelation(Oid indexoid,
values[Anum_pg_index_indnatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexAttrs);
values[Anum_pg_index_indnkeyatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexKeyAttrs);
values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique);
+ values[Anum_pg_index_indnullsnotdistinct - 1] = BoolGetDatum(indexInfo->ii_NullsNotDistinct);
values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary);
values[Anum_pg_index_indisexclusion - 1] = BoolGetDatum(isexclusion);
values[Anum_pg_index_indimmediate - 1] = BoolGetDatum(immediate);
@@ -1349,6 +1350,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
indexExprs,
indexPreds,
oldInfo->ii_Unique,
+ oldInfo->ii_NullsNotDistinct,
false, /* not ready for inserts */
true);
@@ -2410,6 +2412,7 @@ BuildIndexInfo(Relation index)
RelationGetIndexExpressions(index),
RelationGetIndexPredicate(index),
indexStruct->indisunique,
+ indexStruct->indnullsnotdistinct,
indexStruct->indisready,
false);
@@ -2469,6 +2472,7 @@ BuildDummyIndexInfo(Relation index)
RelationGetDummyIndexExpressions(index),
NIL,
indexStruct->indisunique,
+ indexStruct->indnullsnotdistinct,
indexStruct->indisready,
false);
@@ -2502,6 +2506,9 @@ CompareIndexInfo(IndexInfo *info1, IndexInfo *info2,
if (info1->ii_Unique != info2->ii_Unique)
return false;
+ if (info1->ii_NullsNotDistinct != info2->ii_NullsNotDistinct)
+ return false;
+
/* indexes are only equivalent if they have the same access method */
if (info1->ii_Am != info2->ii_Am)
return false;
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 11d9dd60c2..caafb00c44 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1838,7 +1838,11 @@ CREATE VIEW table_constraints AS
AS is_deferrable,
CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
AS initially_deferred,
- CAST('YES' AS yes_or_no) AS enforced
+ CAST('YES' AS yes_or_no) AS enforced,
+ CAST(CASE WHEN c.contype = 'u'
+ THEN CASE WHEN (SELECT NOT indnullsnotdistinct FROM pg_index WHERE indexrelid = conindid) THEN 'YES' ELSE 'NO' END
+ END
+ AS yes_or_no) AS nulls_distinct
FROM pg_namespace nc,
pg_namespace nr,
@@ -1868,7 +1872,8 @@ CREATE VIEW table_constraints AS
CAST('CHECK' AS character_data) AS constraint_type,
CAST('NO' AS yes_or_no) AS is_deferrable,
CAST('NO' AS yes_or_no) AS initially_deferred,
- CAST('YES' AS yes_or_no) AS enforced
+ CAST('YES' AS yes_or_no) AS enforced,
+ CAST(NULL AS yes_or_no) AS nulls_distinct
FROM pg_namespace nr,
pg_class r,
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index b8a78f4d41..097d9c4784 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -228,6 +228,7 @@ F263 Comma-separated predicates in simple CASE expression NO
F271 Compound character literals YES
F281 LIKE enhancements YES
F291 UNIQUE predicate NO
+F292 UNIQUE null treatment YES SQL:202x draft
F301 CORRESPONDING in query expressions NO
F302 INTERSECT table operator YES
F302 INTERSECT table operator 01 INTERSECT DISTINCT table operator YES
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 0db90c2011..9614f64b92 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -301,6 +301,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
indexInfo->ii_ExclusionStrats = NULL;
indexInfo->ii_OpclassOptions = NULL;
indexInfo->ii_Unique = true;
+ indexInfo->ii_NullsNotDistinct = false;
indexInfo->ii_ReadyForInserts = true;
indexInfo->ii_Concurrent = false;
indexInfo->ii_BrokenHotChain = false;
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 8d3104821e..875880a21f 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -226,7 +226,7 @@ CheckIndexCompatible(Oid oldId,
* ii_NumIndexKeyAttrs with same value.
*/
indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes,
- accessMethodId, NIL, NIL, false, false, false);
+ accessMethodId, NIL, NIL, false, false, false, false);
typeObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
collationObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
@@ -867,6 +867,7 @@ DefineIndex(Oid relationId,
NIL, /* expressions, NIL for now */
make_ands_implicit((Expr *) stmt->whereClause),
stmt->unique,
+ stmt->nulls_not_distinct,
!concurrent,
concurrent);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index df0b747883..fab86b605b 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3068,6 +3068,7 @@ _copyConstraint(const Constraint *from)
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
COPY_SCALAR_FIELD(generated_when);
+ COPY_SCALAR_FIELD(nulls_not_distinct);
COPY_NODE_FIELD(keys);
COPY_NODE_FIELD(including);
COPY_NODE_FIELD(exclusions);
@@ -3660,6 +3661,7 @@ _copyIndexStmt(const IndexStmt *from)
COPY_SCALAR_FIELD(oldCreateSubid);
COPY_SCALAR_FIELD(oldFirstRelfilenodeSubid);
COPY_SCALAR_FIELD(unique);
+ COPY_SCALAR_FIELD(nulls_not_distinct);
COPY_SCALAR_FIELD(primary);
COPY_SCALAR_FIELD(isconstraint);
COPY_SCALAR_FIELD(deferrable);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index cb7ddd463c..0544f220f5 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1400,6 +1400,7 @@ _equalIndexStmt(const IndexStmt *a, const IndexStmt *b)
COMPARE_SCALAR_FIELD(oldCreateSubid);
COMPARE_SCALAR_FIELD(oldFirstRelfilenodeSubid);
COMPARE_SCALAR_FIELD(unique);
+ COMPARE_SCALAR_FIELD(nulls_not_distinct);
COMPARE_SCALAR_FIELD(primary);
COMPARE_SCALAR_FIELD(isconstraint);
COMPARE_SCALAR_FIELD(deferrable);
@@ -2710,6 +2711,7 @@ _equalConstraint(const Constraint *a, const Constraint *b)
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
COMPARE_SCALAR_FIELD(generated_when);
+ COMPARE_SCALAR_FIELD(nulls_not_distinct);
COMPARE_NODE_FIELD(keys);
COMPARE_NODE_FIELD(including);
COMPARE_NODE_FIELD(exclusions);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 7d1a01d1ed..4967414a7d 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -741,7 +741,7 @@ make_ands_implicit(Expr *clause)
*/
IndexInfo *
makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
- List *predicates, bool unique, bool isready, bool concurrent)
+ List *predicates, bool unique, bool nulls_not_distinct, bool isready, bool concurrent)
{
IndexInfo *n = makeNode(IndexInfo);
@@ -750,6 +750,7 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
Assert(n->ii_NumIndexKeyAttrs != 0);
Assert(n->ii_NumIndexKeyAttrs <= n->ii_NumIndexAttrs);
n->ii_Unique = unique;
+ n->ii_NullsNotDistinct = nulls_not_distinct;
n->ii_ReadyForInserts = isready;
n->ii_Concurrent = concurrent;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 91a89b6d51..8a9f216963 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2774,6 +2774,7 @@ _outIndexStmt(StringInfo str, const IndexStmt *node)
WRITE_UINT_FIELD(oldCreateSubid);
WRITE_UINT_FIELD(oldFirstRelfilenodeSubid);
WRITE_BOOL_FIELD(unique);
+ WRITE_BOOL_FIELD(nulls_not_distinct);
WRITE_BOOL_FIELD(primary);
WRITE_BOOL_FIELD(isconstraint);
WRITE_BOOL_FIELD(deferrable);
@@ -3706,6 +3707,7 @@ _outConstraint(StringInfo str, const Constraint *node)
case CONSTR_UNIQUE:
appendStringInfoString(str, "UNIQUE");
+ WRITE_BOOL_FIELD(nulls_not_distinct);
WRITE_NODE_FIELD(keys);
WRITE_NODE_FIELD(including);
WRITE_NODE_FIELD(options);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 3d4dd43e47..0490e5b9a9 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -625,6 +625,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
+%type <boolean> opt_unique_null_treatment
%type <ival> generated_when override_kind
%type <partspec> PartitionSpec OptPartitionSpec
%type <partelem> part_elem
@@ -3623,15 +3624,16 @@ ColConstraintElem:
n->location = @1;
$$ = (Node *)n;
}
- | UNIQUE opt_definition OptConsTableSpace
+ | UNIQUE opt_unique_null_treatment opt_definition OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
n->location = @1;
+ n->nulls_not_distinct = !$2;
n->keys = NULL;
- n->options = $2;
+ n->options = $3;
n->indexname = NULL;
- n->indexspace = $3;
+ n->indexspace = $4;
$$ = (Node *)n;
}
| PRIMARY KEY opt_definition OptConsTableSpace
@@ -3716,6 +3718,12 @@ ColConstraintElem:
}
;
+opt_unique_null_treatment:
+ NULLS_P DISTINCT { $$ = true; }
+ | NULLS_P NOT DISTINCT { $$ = false; }
+ | /*EMPTY*/ { $$ = true; }
+ ;
+
generated_when:
ALWAYS { $$ = ATTRIBUTE_IDENTITY_ALWAYS; }
| BY DEFAULT { $$ = ATTRIBUTE_IDENTITY_BY_DEFAULT; }
@@ -3828,18 +3836,19 @@ ConstraintElem:
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
}
- | UNIQUE '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
+ | UNIQUE opt_unique_null_treatment '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
ConstraintAttributeSpec
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
n->location = @1;
- n->keys = $3;
- n->including = $5;
- n->options = $6;
+ n->nulls_not_distinct = !$2;
+ n->keys = $4;
+ n->including = $6;
+ n->options = $7;
n->indexname = NULL;
- n->indexspace = $7;
- processCASbits($8, @8, "UNIQUE",
+ n->indexspace = $8;
+ processCASbits($9, @9, "UNIQUE",
&n->deferrable, &n->initdeferred, NULL,
NULL, yyscanner);
$$ = (Node *)n;
@@ -7413,7 +7422,7 @@ defacl_privilege_target:
IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
@@ -7423,9 +7432,10 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->accessMethod = $8;
n->indexParams = $10;
n->indexIncludingParams = $12;
- n->options = $13;
- n->tableSpace = $14;
- n->whereClause = $15;
+ n->nulls_not_distinct = !$13;
+ n->options = $14;
+ n->tableSpace = $15;
+ n->whereClause = $16;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -7443,7 +7453,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
}
| CREATE opt_unique INDEX opt_concurrently IF_P NOT EXISTS name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
@@ -7453,9 +7463,10 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->accessMethod = $11;
n->indexParams = $13;
n->indexIncludingParams = $15;
- n->options = $16;
- n->tableSpace = $17;
- n->whereClause = $18;
+ n->nulls_not_distinct = !$16;
+ n->options = $17;
+ n->tableSpace = $18;
+ n->whereClause = $19;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -13804,7 +13815,7 @@ a_expr: c_expr { $$ = $1; }
else
$$ = (Node *) makeA_Expr(AEXPR_OP_ALL, $2, $1, $5, @2);
}
- | UNIQUE select_with_parens
+ | UNIQUE opt_unique_null_treatment select_with_parens
{
/* Not sure how to get rid of the parentheses
* but there are lots of shift/reduce errors without them.
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 2d857a301b..9964450466 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;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 8da525c715..606279f3b5 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1442,6 +1442,9 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
{
appendStringInfoChar(&buf, ')');
+ if (idxrec->indnullsnotdistinct)
+ appendStringInfo(&buf, " NULLS NOT DISTINCT");
+
/*
* If it has options, append "WITH (options)"
*/
@@ -2310,9 +2313,20 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
/* Start off the constraint definition */
if (conForm->contype == CONSTRAINT_PRIMARY)
- appendStringInfoString(&buf, "PRIMARY KEY (");
+ appendStringInfoString(&buf, "PRIMARY KEY ");
else
- appendStringInfoString(&buf, "UNIQUE (");
+ appendStringInfoString(&buf, "UNIQUE ");
+
+ indexId = conForm->conindid;
+
+ indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId));
+ if (!HeapTupleIsValid(indtup))
+ elog(ERROR, "cache lookup failed for index %u", indexId);
+ if (conForm->contype == CONSTRAINT_UNIQUE &&
+ ((Form_pg_index) GETSTRUCT(indtup))->indnullsnotdistinct)
+ appendStringInfoString(&buf, "NULLS NOT DISTINCT ");
+
+ appendStringInfoString(&buf, "(");
/* Fetch and build target column list */
val = SysCacheGetAttr(CONSTROID, tup,
@@ -2325,12 +2339,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
appendStringInfoChar(&buf, ')');
- indexId = conForm->conindid;
-
/* Build including column list (from pg_index.indkeys) */
- indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId));
- if (!HeapTupleIsValid(indtup))
- elog(ERROR, "cache lookup failed for index %u", indexId);
val = SysCacheGetAttr(INDEXRELID, indtup,
Anum_pg_index_indnatts, &isnull);
if (isnull)
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 105d8d4601..a389ce0445 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2274,6 +2274,7 @@ RelationReloadIndexInfo(Relation relation)
* the array fields are allowed to change, though.
*/
relation->rd_index->indisunique = index->indisunique;
+ relation->rd_index->indnullsnotdistinct = index->indnullsnotdistinct;
relation->rd_index->indisprimary = index->indisprimary;
relation->rd_index->indisexclusion = index->indisexclusion;
relation->rd_index->indimmediate = index->indimmediate;
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index 90e26745df..d9a8b86fb9 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -459,6 +459,7 @@ struct Tuplesortstate
/* These are specific to the index_btree subcase: */
bool enforceUnique; /* complain if we find duplicate tuples */
+ bool uniqueNullsNotDistinct; /* unique constraint null treatment */
/* These are specific to the index_hash subcase: */
uint32 high_mask; /* masks for sortable part of hash code */
@@ -1065,6 +1066,7 @@ Tuplesortstate *
tuplesort_begin_index_btree(Relation heapRel,
Relation indexRel,
bool enforceUnique,
+ bool uniqueNullsNotDistinct,
int workMem,
SortCoordinate coordinate,
bool randomAccess)
@@ -1103,6 +1105,7 @@ tuplesort_begin_index_btree(Relation heapRel,
state->heapRel = heapRel;
state->indexRel = indexRel;
state->enforceUnique = enforceUnique;
+ state->uniqueNullsNotDistinct = uniqueNullsNotDistinct;
indexScanKey = _bt_mkscankey(indexRel, NULL);
@@ -4200,14 +4203,15 @@ comparetup_index_btree(const SortTuple *a, const SortTuple *b,
/*
* If btree has asked us to enforce uniqueness, complain if two equal
- * tuples are detected (unless there was at least one NULL field).
+ * tuples are detected (unless there was at least one NULL field and NULLS
+ * NOT DISTINCT was not set).
*
* It is sufficient to make the test here, because if two tuples are equal
* they *must* get compared at some stage of the sort --- otherwise the
* sort algorithm wouldn't have checked whether one must appear before the
* other.
*/
- if (state->enforceUnique && !equal_hasnull)
+ if (state->enforceUnique && !(!state->uniqueNullsNotDistinct && equal_hasnull))
{
Datum values[INDEX_MAX_KEYS];
bool isnull[INDEX_MAX_KEYS];
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index b52f3ccda2..82a4521ce7 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6467,6 +6467,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_indkey,
i_indisclustered,
i_indisreplident,
+ i_indnullsnotdistinct,
i_contype,
i_conname,
i_condeferrable,
@@ -6543,14 +6544,21 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"(SELECT pg_catalog.array_agg(attstattarget ORDER BY attnum) "
" FROM pg_catalog.pg_attribute "
" WHERE attrelid = i.indexrelid AND "
- " attstattarget >= 0) AS indstatvals ");
+ " attstattarget >= 0) AS indstatvals, ");
else
appendPQExpBuffer(query,
"0 AS parentidx, "
"i.indnatts AS indnkeyatts, "
"i.indnatts AS indnatts, "
"'' AS indstatcols, "
- "'' AS indstatvals ");
+ "'' AS indstatvals, ");
+
+ if (fout->remoteVersion >= 150000)
+ appendPQExpBuffer(query,
+ "i.indnullsnotdistinct ");
+ else
+ appendPQExpBuffer(query,
+ "false AS indnullsnotdistinct ");
/*
* The point of the messy-looking outer join is to find a constraint that
@@ -6613,6 +6621,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_indkey = PQfnumber(res, "indkey");
i_indisclustered = PQfnumber(res, "indisclustered");
i_indisreplident = PQfnumber(res, "indisreplident");
+ i_indnullsnotdistinct = PQfnumber(res, "indnullsnotdistinct");
i_contype = PQfnumber(res, "contype");
i_conname = PQfnumber(res, "conname");
i_condeferrable = PQfnumber(res, "condeferrable");
@@ -6689,6 +6698,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
indxinfo[j].indkeys, indxinfo[j].indnattrs);
indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
indxinfo[j].indisreplident = (PQgetvalue(res, j, i_indisreplident)[0] == 't');
+ indxinfo[j].indnullsnotdistinct = (PQgetvalue(res, j, i_indnullsnotdistinct)[0] == 't');
indxinfo[j].parentidx = atooid(PQgetvalue(res, j, i_parentidx));
indxinfo[j].partattaches = (SimplePtrList)
{
@@ -16050,8 +16060,11 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
}
else
{
- appendPQExpBuffer(q, "%s (",
+ appendPQExpBuffer(q, "%s",
coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
+ if (indxinfo->indnullsnotdistinct)
+ appendPQExpBuffer(q, " NULLS NOT DISTINCT");
+ appendPQExpBuffer(q, " (");
for (k = 0; k < indxinfo->indnkeyattrs; k++)
{
int indkey = (int) indxinfo->indkeys[k];
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index f011ace8a8..06a2979f04 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -398,6 +398,7 @@ typedef struct _indxInfo
* contains both key and nonkey attributes */
bool indisclustered;
bool indisreplident;
+ bool indnullsnotdistinct;
Oid parentidx; /* if a partition, parent index OID */
SimplePtrList partattaches; /* if partitioned, partition attach objects */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index c28788e84f..a59200a792 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2181,6 +2181,11 @@ describeOneTableDetails(const char *schemaname,
else
appendPQExpBufferStr(&buf, "false AS indisreplident,\n");
+ if (pset.sversion >= 150000)
+ appendPQExpBufferStr(&buf, "i.indnullsnotdistinct,\n");
+ else
+ appendPQExpBufferStr(&buf, "false AS indnullsnotdistinct,\n");
+
appendPQExpBuffer(&buf, " a.amname, c2.relname, "
"pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
"FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
@@ -2205,14 +2210,20 @@ describeOneTableDetails(const char *schemaname,
char *deferrable = PQgetvalue(result, 0, 4);
char *deferred = PQgetvalue(result, 0, 5);
char *indisreplident = PQgetvalue(result, 0, 6);
- char *indamname = PQgetvalue(result, 0, 7);
- char *indtable = PQgetvalue(result, 0, 8);
- char *indpred = PQgetvalue(result, 0, 9);
+ char *indnullsnotdistinct = PQgetvalue(result, 0, 7);
+ char *indamname = PQgetvalue(result, 0, 8);
+ char *indtable = PQgetvalue(result, 0, 9);
+ char *indpred = PQgetvalue(result, 0, 10);
if (strcmp(indisprimary, "t") == 0)
printfPQExpBuffer(&tmpbuf, _("primary key, "));
else if (strcmp(indisunique, "t") == 0)
- printfPQExpBuffer(&tmpbuf, _("unique, "));
+ {
+ printfPQExpBuffer(&tmpbuf, _("unique"));
+ if (strcmp(indnullsnotdistinct, "t") == 0)
+ appendPQExpBufferStr(&tmpbuf, _(" nulls not distinct"));
+ appendPQExpBuffer(&tmpbuf, _(", "));
+ }
else
resetPQExpBuffer(&tmpbuf);
appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index a5a6075d4d..8c8300284e 100644
--- a/src/include/catalog/pg_index.h
+++ b/src/include/catalog/pg_index.h
@@ -34,6 +34,7 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO
int16 indnatts; /* total number of columns in index */
int16 indnkeyatts; /* number of key columns in index */
bool indisunique; /* is this a unique index? */
+ bool indnullsnotdistinct; /* null treatment in unique index */
bool indisprimary; /* is this index for primary key? */
bool indisexclusion; /* is this index for exclusion constraint? */
bool indimmediate; /* is uniqueness enforced immediately? */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index ddc3529332..2b628d0ef4 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -171,6 +171,7 @@ typedef struct IndexInfo
uint16 *ii_UniqueStrats; /* array with one entry per column */
Datum *ii_OpclassOptions; /* array with one entry per column */
bool ii_Unique;
+ bool ii_NullsNotDistinct;
bool ii_ReadyForInserts;
bool ii_Concurrent;
bool ii_BrokenHotChain;
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index eea87f847d..9edacd921b 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -96,7 +96,7 @@ extern List *make_ands_implicit(Expr *clause);
extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid,
List *expressions, List *predicates,
- bool unique, bool isready, bool concurrent);
+ bool unique, bool nulls_not_distinct, bool isready, bool concurrent);
extern DefElem *makeDefElem(char *name, Node *arg, int location);
extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 4c5a8a39bf..24ec61ce87 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2276,6 +2276,7 @@ typedef struct Constraint
char generated_when; /* ALWAYS or BY DEFAULT */
/* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */
+ bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
List *keys; /* String nodes naming referenced key
* column(s) */
List *including; /* String nodes naming referenced nonkey
@@ -2904,6 +2905,7 @@ typedef struct IndexStmt
SubTransactionId oldFirstRelfilenodeSubid; /* rd_firstRelfilenodeSubid of
* oldNode */
bool unique; /* is index unique? */
+ bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
bool primary; /* is index a primary key? */
bool isconstraint; /* is it for a pkey/unique constraint? */
bool deferrable; /* is the constraint DEFERRABLE? */
diff --git a/src/include/utils/tuplesort.h b/src/include/utils/tuplesort.h
index f94949370b..b30ca09598 100644
--- a/src/include/utils/tuplesort.h
+++ b/src/include/utils/tuplesort.h
@@ -208,6 +208,7 @@ extern Tuplesortstate *tuplesort_begin_cluster(TupleDesc tupDesc,
extern Tuplesortstate *tuplesort_begin_index_btree(Relation heapRel,
Relation indexRel,
bool enforceUnique,
+ bool uniqueNullsNotDistinct,
int workMem, SortCoordinate coordinate,
bool randomAccess);
extern Tuplesortstate *tuplesort_begin_index_hash(Relation heapRel,
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e32cf8bb57..36ccbb5f15 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -445,6 +445,29 @@ SELECT * FROM UNIQUE_TBL;
6 | six-upsert-insert
(7 rows)
+DROP TABLE UNIQUE_TBL;
+CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+INSERT INTO UNIQUE_TBL VALUES (2, 'two');
+INSERT INTO UNIQUE_TBL VALUES (1, 'three');
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(1) already exists.
+INSERT INTO UNIQUE_TBL VALUES (4, 'four');
+INSERT INTO UNIQUE_TBL VALUES (5, 'one');
+INSERT INTO UNIQUE_TBL (t) VALUES ('six');
+INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(null) already exists.
+SELECT * FROM UNIQUE_TBL;
+ i | t
+---+------
+ 1 | one
+ 2 | two
+ 4 | four
+ 5 | one
+ | six
+(5 rows)
+
DROP TABLE UNIQUE_TBL;
CREATE TABLE UNIQUE_TBL (i int, t text,
UNIQUE(i,t));
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index daf75dd5c4..53c8e830ce 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1272,6 +1272,67 @@ SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
DROP INDEX hash_tuplesort_idx;
RESET maintenance_work_mem;
--
+-- Test unique null behavior
+--
+CREATE TABLE unique_tbl (i int, t text);
+CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
+CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;
+INSERT INTO unique_tbl VALUES (1, 'one');
+INSERT INTO unique_tbl VALUES (2, 'two');
+INSERT INTO unique_tbl VALUES (3, 'three');
+INSERT INTO unique_tbl VALUES (4, 'four');
+INSERT INTO unique_tbl VALUES (5, 'one');
+INSERT INTO unique_tbl (t) VALUES ('six');
+INSERT INTO unique_tbl (t) VALUES ('seven'); -- error from unique_idx2
+ERROR: duplicate key value violates unique constraint "unique_idx2"
+DETAIL: Key (i)=(null) already exists.
+DROP INDEX unique_idx1, unique_idx2;
+INSERT INTO unique_tbl (t) VALUES ('seven');
+-- build indexes on filled table
+CREATE UNIQUE INDEX unique_idx3 ON unique_tbl (i) NULLS DISTINCT; -- ok
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- error
+ERROR: could not create unique index "unique_idx4"
+DETAIL: Key (i)=(null) is duplicated.
+DELETE FROM unique_tbl WHERE t = 'seven';
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- ok now
+\d unique_tbl
+ Table "public.unique_tbl"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ i | integer | | |
+ t | text | | |
+Indexes:
+ "unique_idx3" UNIQUE, btree (i)
+ "unique_idx4" UNIQUE, btree (i) NULLS NOT DISTINCT
+
+\d unique_idx3
+ Index "public.unique_idx3"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ i | integer | yes | i
+unique, btree, for table "public.unique_tbl"
+
+\d unique_idx4
+ Index "public.unique_idx4"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ i | integer | yes | i
+unique nulls not distinct, btree, for table "public.unique_tbl"
+
+SELECT pg_get_indexdef('unique_idx3'::regclass);
+ pg_get_indexdef
+----------------------------------------------------------------------
+ CREATE UNIQUE INDEX unique_idx3 ON public.unique_tbl USING btree (i)
+(1 row)
+
+SELECT pg_get_indexdef('unique_idx4'::regclass);
+ pg_get_indexdef
+-----------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX unique_idx4 ON public.unique_tbl USING btree (i) NULLS NOT DISTINCT
+(1 row)
+
+DROP TABLE unique_tbl;
+--
-- Test functional index
--
CREATE TABLE func_index_heap (f1 text, f2 text);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 458f805778..34de0c969a 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -306,6 +306,20 @@ CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text);
DROP TABLE UNIQUE_TBL;
+CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
+
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+INSERT INTO UNIQUE_TBL VALUES (2, 'two');
+INSERT INTO UNIQUE_TBL VALUES (1, 'three');
+INSERT INTO UNIQUE_TBL VALUES (4, 'four');
+INSERT INTO UNIQUE_TBL VALUES (5, 'one');
+INSERT INTO UNIQUE_TBL (t) VALUES ('six');
+INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
+
+SELECT * FROM UNIQUE_TBL;
+
+DROP TABLE UNIQUE_TBL;
+
CREATE TABLE UNIQUE_TBL (i int, t text,
UNIQUE(i,t));
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 8b353be16e..9003950a1f 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -387,6 +387,43 @@ CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fi
RESET maintenance_work_mem;
+--
+-- Test unique null behavior
+--
+CREATE TABLE unique_tbl (i int, t text);
+
+CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
+CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;
+
+INSERT INTO unique_tbl VALUES (1, 'one');
+INSERT INTO unique_tbl VALUES (2, 'two');
+INSERT INTO unique_tbl VALUES (3, 'three');
+INSERT INTO unique_tbl VALUES (4, 'four');
+INSERT INTO unique_tbl VALUES (5, 'one');
+INSERT INTO unique_tbl (t) VALUES ('six');
+INSERT INTO unique_tbl (t) VALUES ('seven'); -- error from unique_idx2
+
+DROP INDEX unique_idx1, unique_idx2;
+
+INSERT INTO unique_tbl (t) VALUES ('seven');
+
+-- build indexes on filled table
+CREATE UNIQUE INDEX unique_idx3 ON unique_tbl (i) NULLS DISTINCT; -- ok
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- error
+
+DELETE FROM unique_tbl WHERE t = 'seven';
+
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- ok now
+
+\d unique_tbl
+\d unique_idx3
+\d unique_idx4
+SELECT pg_get_indexdef('unique_idx3'::regclass);
+SELECT pg_get_indexdef('unique_idx4'::regclass);
+
+DROP TABLE unique_tbl;
+
+
--
-- Test functional index
--
base-commit: cab5b9ab2c066ba904f13de2681872dcda31e207
--
2.34.1
Re: UNIQUE null treatment option
Hi,
bool isunique;
+ bool nulls_not_distinct;
} BTSpool;
Looking at the other fields in BTSpool, there is no underscore in field
name.
I think the new field can be named nullsdistinct. This way, the
double negative is avoided.
Similar comment for new fields in BTShared and BTLeader.
And the naming would be consistent with information_schema.sql where
nulls_distinct is used:
+ CAST('YES' AS yes_or_no) AS enforced,
+ CAST(NULL AS yes_or_no) AS nulls_distinctCheers
Import Notes
Resolved by subject fallback
Re: UNIQUE null treatment option
+1 for commiting this feature. Consider this useful.
--
Best regards,
Pavel Borisov
Postgres Professional: http://postgrespro.com <http://www.postgrespro.com>
Re: UNIQUE null treatment option
I find this patch useful. It includes changes in documentation and tests.
Code itself looks reasonable to me. Since, unique constraint check is done
by corresponding btree index, it makes this feature implementation
elegant and lightweight.
In my view, it is sufficient that heap relation can have different nulls
treatment in unique constraints for different unique columns. For example:
CREATE TABLE t (i INT UNIQUE NULLS DISTINCT, a INT UNIQUE NULLS NOT
DISTINCT);
All the tests are running ok on Linux and MacOS X.
Although, patch doesn't apply with default git apply options. Only with the
"three way merge" option (-3). Consider rebasing it, please. Then, in my
view, it can be "Ready for committer".
--
Best regards,
Maxim Orlov.
Re: UNIQUE null treatment option
On Wed, Dec 29, 2021 at 2:06 AM Peter Eisentraut
<peter.eisentraut@enterprisedb.com> wrote:
Here is a rebased version of this patch.
BTScanInsertData.anynullkeys already effectively means "if the index
is a unique index, then we don't actually need to go through
_bt_check_unique(), or perform any other checkingunique steps". This
is really an instruction about what to do (or not do), based on the
specifics of the values for the insertion scan key plus the index
definition. In other words, the code in _bt_mkscankey() that sets up
BTScanInsertData (an insertion scankey) was written with the exact
requirements of btinsert() in mind -- nothing more.
I wonder if the logic for setting BTScanInsertData.anynullkeys inside
_bt_mkscankey() is the place to put your test for
rel->rd_index->indnullsnotdistinct -- not inside _bt_doinsert(). That
would probably necessitate renaming anynullkeys, but that's okay. This
feels more natural to me because a NULL key column in a NULLS NOT
DISTINCT unique constraint is very similar to a NULL non-key column in
an INCLUDE index, as far as our requirements go -- and so both cases
should probably be dealt with at the same point.
--
Peter Geoghegan
Re: UNIQUE null treatment option
On Thu, Jan 13, 2022 at 10:36 AM Peter Geoghegan <pg@bowt.ie> wrote:
I wonder if the logic for setting BTScanInsertData.anynullkeys inside
_bt_mkscankey() is the place to put your test for
rel->rd_index->indnullsnotdistinct -- not inside _bt_doinsert(). That
would probably necessitate renaming anynullkeys, but that's okay. This
feels more natural to me because a NULL key column in a NULLS NOT
DISTINCT unique constraint is very similar to a NULL non-key column in
an INCLUDE index, as far as our requirements go -- and so both cases
should probably be dealt with at the same point.
Correction: I meant to write "...a NULL key column in a NULLS DISTINCT
unique constraint is very similar...".
--
Peter Geoghegan
Re: UNIQUE null treatment option
I wonder if the logic for setting BTScanInsertData.anynullkeys inside
_bt_mkscankey() is the place to put your test for
rel->rd_index->indnullsnotdistinct -- not inside _bt_doinsert(). That
would probably necessitate renaming anynullkeys, but that's okay. This
feels more natural to me because a NULL key column in a NULLS NOT
DISTINCT unique constraint is very similar to a NULL non-key column in
an INCLUDE index, as far as our requirements go -- and so both cases
should probably be dealt with at the same point.
A good point, indeed!
--
Best regards,
Pavel Borisov
Postgres Professional: http://postgrespro.com <http://www.postgrespro.com>
Re: UNIQUE null treatment option
On 13.01.22 19:36, Peter Geoghegan wrote:
I wonder if the logic for setting BTScanInsertData.anynullkeys inside
_bt_mkscankey() is the place to put your test for
rel->rd_index->indnullsnotdistinct -- not inside _bt_doinsert(). That
would probably necessitate renaming anynullkeys, but that's okay. This
feels more natural to me because a NULL key column in a NULLS NOT
DISTINCT unique constraint is very similar to a NULL non-key column in
an INCLUDE index, as far as our requirements go -- and so both cases
should probably be dealt with at the same point.
Makes sense. Here is an updated patch with this change.
I didn't end up renaming anynullkeys. I came up with names like
"anyalwaysdistinctkeys", but in the end that felt too abstract, and
moreover, it would require rewriting a bunch of code comments that refer
to null values in this context. Since as you wrote, anynullkeys is just
a local concern between two functions, this slight inaccuracy is perhaps
better than some highly general but unclear terminology.
Attachments:
v3-0001-Add-UNIQUE-null-treatment-option.patchtext/plain; charset=UTF-8; name=v3-0001-Add-UNIQUE-null-treatment-option.patchDownload
From c07b757ff5a31427de07e394f7c4736cb0a05378 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Mon, 24 Jan 2022 16:47:25 +0100
Subject: [PATCH v3] 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.
XXX 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. But perhaps the double
negatives make some code harder to read.
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 ++--
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/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 +++++++++++++
35 files changed, 347 insertions(+), 56 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 1e65c426b2..2acd6ce98c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4265,6 +4265,19 @@ <title><structname>pg_index</structname> Columns</title>
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>indnullsnotdistinct</structfield> <type>bool</type>
+ </para>
+ <para>
+ 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).
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>indisprimary</structfield> <type>bool</type>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 22f6c5c7ab..c10de43492 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -759,14 +759,33 @@ <title>Unique Constraints</title>
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 <literal>NULLS
+ NOT DISTINCT</literal>, like
+<programlisting>
+CREATE TABLE products (
+ product_no integer UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis>,
+ name text,
+ price numeric
+);
+</programlisting>
+ or
+<programlisting>
+CREATE TABLE products (
+ product_no integer,
+ name text,
+ price numeric,
+ UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis> (product_no)
+);
+</programlisting>
+ The default behavior can be specified explicitly using <literal>NULLS
+ DISTINCT</literal>. 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.
</para>
</sect2>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index c5e68c175f..350c75bc31 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -6899,6 +6899,18 @@ <title><structname>table_constraints</structname> Columns</title>
<literal>YES</literal>)
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>nulls_distinct</structfield> <type>yes_or_no</type>
+ </para>
+ <para>
+ If the constraint is a unique constraint, then <literal>YES</literal>
+ if the constraint treats nulls as distinct or <literal>NO</literal> if
+ it treats nulls as not distinct, otherwise null for other types of
+ constraints.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index a76e2e7322..b20f6c651c 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -103,7 +103,7 @@
DEFAULT <replaceable>default_expr</replaceable> |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
- UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
@@ -113,7 +113,7 @@
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 89a4d746f6..91eaaabc90 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -24,6 +24,7 @@
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> [ ( <replaceable class="parameter">opclass_parameter</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+ [ NULLS [ NOT ] DISTINCT ]
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
@@ -334,6 +335,18 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>NULLS DISTINCT</literal></term>
+ <term><literal>NULLS NOT DISTINCT</literal></term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">storage_parameter</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index b97bb9ded1..7e4ef312c0 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -67,7 +67,7 @@
DEFAULT <replaceable>default_expr</replaceable> |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
- UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
@@ -77,7 +77,7 @@
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
@@ -917,8 +917,8 @@ <title>Parameters</title>
</varlistentry>
<varlistentry>
- <term><literal>UNIQUE</literal> (column constraint)</term>
- <term><literal>UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
+ <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ]</literal> (column constraint)</term>
+ <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
<optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
<listitem>
@@ -934,7 +934,8 @@ <title>Parameters</title>
<para>
For the purpose of a unique constraint, null values are not
- considered equal.
+ considered equal, unless <literal>NULLS NOT DISTINCT</literal> is
+ specified.
</para>
<para>
diff --git a/src/backend/access/nbtree/nbtinsert.c b/src/backend/access/nbtree/nbtinsert.c
index 62746c4721..68628ec000 100644
--- a/src/backend/access/nbtree/nbtinsert.c
+++ b/src/backend/access/nbtree/nbtinsert.c
@@ -398,9 +398,9 @@ _bt_search_insert(Relation rel, BTInsertState insertstate)
* _bt_findinsertloc() to reuse most of the binary search work we do
* here.
*
- * Do not call here when there are NULL values in scan key. NULL should be
- * considered unequal to NULL when checking for duplicates, but we are not
- * prepared to handle that correctly.
+ * This code treats NULLs as equal, unlike the default semantics for unique
+ * indexes. So do not call here when there are NULL values in scan key and
+ * the index uses the default NULLS DISTINCT mode.
*/
static TransactionId
_bt_check_unique(Relation rel, BTInsertState insertstate, Relation heapRel,
diff --git a/src/backend/access/nbtree/nbtsort.c b/src/backend/access/nbtree/nbtsort.c
index dc220146fd..8a19de2f66 100644
--- a/src/backend/access/nbtree/nbtsort.c
+++ b/src/backend/access/nbtree/nbtsort.c
@@ -89,6 +89,7 @@ typedef struct BTSpool
Relation heap;
Relation index;
bool isunique;
+ bool nulls_not_distinct;
} BTSpool;
/*
@@ -106,6 +107,7 @@ typedef struct BTShared
Oid heaprelid;
Oid indexrelid;
bool isunique;
+ bool nulls_not_distinct;
bool isconcurrent;
int scantuplesortstates;
@@ -206,6 +208,7 @@ typedef struct BTLeader
typedef struct BTBuildState
{
bool isunique;
+ bool nulls_not_distinct;
bool havedead;
Relation heap;
BTSpool *spool;
@@ -307,6 +310,7 @@ btbuild(Relation heap, Relation index, IndexInfo *indexInfo)
#endif /* BTREE_BUILD_STATS */
buildstate.isunique = indexInfo->ii_Unique;
+ buildstate.nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
buildstate.havedead = false;
buildstate.heap = heap;
buildstate.spool = NULL;
@@ -380,6 +384,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
btspool->heap = heap;
btspool->index = index;
btspool->isunique = indexInfo->ii_Unique;
+ btspool->nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
/* Save as primary spool */
buildstate->spool = btspool;
@@ -429,6 +434,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
*/
buildstate->spool->sortstate =
tuplesort_begin_index_btree(heap, index, buildstate->isunique,
+ buildstate->nulls_not_distinct,
maintenance_work_mem, coordinate,
false);
@@ -468,7 +474,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
* full, so we give it only work_mem
*/
buildstate->spool2->sortstate =
- tuplesort_begin_index_btree(heap, index, false, work_mem,
+ tuplesort_begin_index_btree(heap, index, false, false, work_mem,
coordinate2, false);
}
@@ -1554,6 +1560,7 @@ _bt_begin_parallel(BTBuildState *buildstate, bool isconcurrent, int request)
btshared->heaprelid = RelationGetRelid(btspool->heap);
btshared->indexrelid = RelationGetRelid(btspool->index);
btshared->isunique = btspool->isunique;
+ btshared->nulls_not_distinct = btspool->nulls_not_distinct;
btshared->isconcurrent = isconcurrent;
btshared->scantuplesortstates = scantuplesortstates;
ConditionVariableInit(&btshared->workersdonecv);
@@ -1747,6 +1754,7 @@ _bt_leader_participate_as_worker(BTBuildState *buildstate)
leaderworker->heap = buildstate->spool->heap;
leaderworker->index = buildstate->spool->index;
leaderworker->isunique = buildstate->spool->isunique;
+ leaderworker->nulls_not_distinct = buildstate->spool->nulls_not_distinct;
/* Initialize second spool, if required */
if (!btleader->btshared->isunique)
@@ -1846,6 +1854,7 @@ _bt_parallel_build_main(dsm_segment *seg, shm_toc *toc)
btspool->heap = heapRel;
btspool->index = indexRel;
btspool->isunique = btshared->isunique;
+ btspool->nulls_not_distinct = btshared->nulls_not_distinct;
/* Look up shared state private to tuplesort.c */
sharedsort = shm_toc_lookup(toc, PARALLEL_KEY_TUPLESORT, false);
@@ -1928,6 +1937,7 @@ _bt_parallel_scan_and_sort(BTSpool *btspool, BTSpool *btspool2,
btspool->sortstate = tuplesort_begin_index_btree(btspool->heap,
btspool->index,
btspool->isunique,
+ btspool->nulls_not_distinct,
sortmem, coordinate,
false);
@@ -1950,13 +1960,14 @@ _bt_parallel_scan_and_sort(BTSpool *btspool, BTSpool *btspool2,
coordinate2->nParticipants = -1;
coordinate2->sharedsort = sharedsort2;
btspool2->sortstate =
- tuplesort_begin_index_btree(btspool->heap, btspool->index, false,
+ tuplesort_begin_index_btree(btspool->heap, btspool->index, false, false,
Min(sortmem, work_mem), coordinate2,
false);
}
/* Fill in buildstate for _bt_build_callback() */
buildstate.isunique = btshared->isunique;
+ buildstate.nulls_not_distinct = btshared->nulls_not_distinct;
buildstate.havedead = false;
buildstate.heap = btspool->heap;
buildstate.spool = btspool;
diff --git a/src/backend/access/nbtree/nbtutils.c b/src/backend/access/nbtree/nbtutils.c
index ed67863c56..6a651d8397 100644
--- a/src/backend/access/nbtree/nbtutils.c
+++ b/src/backend/access/nbtree/nbtutils.c
@@ -165,6 +165,13 @@ _bt_mkscankey(Relation rel, IndexTuple itup)
key->anynullkeys = true;
}
+ /*
+ * In NULLS NOT DISTINCT mode, we pretend that there are no null keys, so
+ * that full uniqueness check is done.
+ */
+ if (rel->rd_index->indnullsnotdistinct)
+ key->anynullkeys = false;
+
return key;
}
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 2308d40256..5e3fc2b35d 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -614,6 +614,7 @@ UpdateIndexRelation(Oid indexoid,
values[Anum_pg_index_indnatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexAttrs);
values[Anum_pg_index_indnkeyatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexKeyAttrs);
values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique);
+ values[Anum_pg_index_indnullsnotdistinct - 1] = BoolGetDatum(indexInfo->ii_NullsNotDistinct);
values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary);
values[Anum_pg_index_indisexclusion - 1] = BoolGetDatum(isexclusion);
values[Anum_pg_index_indimmediate - 1] = BoolGetDatum(immediate);
@@ -1368,6 +1369,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
indexExprs,
indexPreds,
oldInfo->ii_Unique,
+ oldInfo->ii_NullsNotDistinct,
false, /* not ready for inserts */
true);
@@ -2440,6 +2442,7 @@ BuildIndexInfo(Relation index)
RelationGetIndexExpressions(index),
RelationGetIndexPredicate(index),
indexStruct->indisunique,
+ indexStruct->indnullsnotdistinct,
indexStruct->indisready,
false);
@@ -2499,6 +2502,7 @@ BuildDummyIndexInfo(Relation index)
RelationGetDummyIndexExpressions(index),
NIL,
indexStruct->indisunique,
+ indexStruct->indnullsnotdistinct,
indexStruct->indisready,
false);
@@ -2532,6 +2536,9 @@ CompareIndexInfo(IndexInfo *info1, IndexInfo *info2,
if (info1->ii_Unique != info2->ii_Unique)
return false;
+ if (info1->ii_NullsNotDistinct != info2->ii_NullsNotDistinct)
+ return false;
+
/* indexes are only equivalent if they have the same access method */
if (info1->ii_Am != info2->ii_Am)
return false;
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index b4f348a24d..c4ef8e78a5 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1838,7 +1838,11 @@ CREATE VIEW table_constraints AS
AS is_deferrable,
CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
AS initially_deferred,
- CAST('YES' AS yes_or_no) AS enforced
+ CAST('YES' AS yes_or_no) AS enforced,
+ CAST(CASE WHEN c.contype = 'u'
+ THEN CASE WHEN (SELECT NOT indnullsnotdistinct FROM pg_index WHERE indexrelid = conindid) THEN 'YES' ELSE 'NO' END
+ END
+ AS yes_or_no) AS nulls_distinct
FROM pg_namespace nc,
pg_namespace nr,
@@ -1868,7 +1872,8 @@ CREATE VIEW table_constraints AS
CAST('CHECK' AS character_data) AS constraint_type,
CAST('NO' AS yes_or_no) AS is_deferrable,
CAST('NO' AS yes_or_no) AS initially_deferred,
- CAST('YES' AS yes_or_no) AS enforced
+ CAST('YES' AS yes_or_no) AS enforced,
+ CAST(NULL AS yes_or_no) AS nulls_distinct
FROM pg_namespace nr,
pg_class r,
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index b8a78f4d41..097d9c4784 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -228,6 +228,7 @@ F263 Comma-separated predicates in simple CASE expression NO
F271 Compound character literals YES
F281 LIKE enhancements YES
F291 UNIQUE predicate NO
+F292 UNIQUE null treatment YES SQL:202x draft
F301 CORRESPONDING in query expressions NO
F302 INTERSECT table operator YES
F302 INTERSECT table operator 01 INTERSECT DISTINCT table operator YES
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 3c27cb1e71..9bc10729b0 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -301,6 +301,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
indexInfo->ii_ExclusionStrats = NULL;
indexInfo->ii_OpclassOptions = NULL;
indexInfo->ii_Unique = true;
+ indexInfo->ii_NullsNotDistinct = false;
indexInfo->ii_ReadyForInserts = true;
indexInfo->ii_CheckedUnchanged = false;
indexInfo->ii_IndexUnchanged = false;
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index e5cf1bde13..c9dc92f380 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -226,7 +226,7 @@ CheckIndexCompatible(Oid oldId,
* ii_NumIndexKeyAttrs with same value.
*/
indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes,
- accessMethodId, NIL, NIL, false, false, false);
+ accessMethodId, NIL, NIL, false, false, false, false);
typeObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
collationObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
@@ -867,6 +867,7 @@ DefineIndex(Oid relationId,
NIL, /* expressions, NIL for now */
make_ands_implicit((Expr *) stmt->whereClause),
stmt->unique,
+ stmt->nulls_not_distinct,
!concurrent,
concurrent);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 90b5da51c9..6bd95bbce2 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3072,6 +3072,7 @@ _copyConstraint(const Constraint *from)
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
COPY_SCALAR_FIELD(generated_when);
+ COPY_SCALAR_FIELD(nulls_not_distinct);
COPY_NODE_FIELD(keys);
COPY_NODE_FIELD(including);
COPY_NODE_FIELD(exclusions);
@@ -3664,6 +3665,7 @@ _copyIndexStmt(const IndexStmt *from)
COPY_SCALAR_FIELD(oldCreateSubid);
COPY_SCALAR_FIELD(oldFirstRelfilenodeSubid);
COPY_SCALAR_FIELD(unique);
+ COPY_SCALAR_FIELD(nulls_not_distinct);
COPY_SCALAR_FIELD(primary);
COPY_SCALAR_FIELD(isconstraint);
COPY_SCALAR_FIELD(deferrable);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 06345da3ba..4126516222 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1400,6 +1400,7 @@ _equalIndexStmt(const IndexStmt *a, const IndexStmt *b)
COMPARE_SCALAR_FIELD(oldCreateSubid);
COMPARE_SCALAR_FIELD(oldFirstRelfilenodeSubid);
COMPARE_SCALAR_FIELD(unique);
+ COMPARE_SCALAR_FIELD(nulls_not_distinct);
COMPARE_SCALAR_FIELD(primary);
COMPARE_SCALAR_FIELD(isconstraint);
COMPARE_SCALAR_FIELD(deferrable);
@@ -2710,6 +2711,7 @@ _equalConstraint(const Constraint *a, const Constraint *b)
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
COMPARE_SCALAR_FIELD(generated_when);
+ COMPARE_SCALAR_FIELD(nulls_not_distinct);
COMPARE_NODE_FIELD(keys);
COMPARE_NODE_FIELD(including);
COMPARE_NODE_FIELD(exclusions);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 822395625b..c85d8fe975 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -741,7 +741,7 @@ make_ands_implicit(Expr *clause)
*/
IndexInfo *
makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
- List *predicates, bool unique, bool isready, bool concurrent)
+ List *predicates, bool unique, bool nulls_not_distinct, bool isready, bool concurrent)
{
IndexInfo *n = makeNode(IndexInfo);
@@ -750,6 +750,7 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
Assert(n->ii_NumIndexKeyAttrs != 0);
Assert(n->ii_NumIndexKeyAttrs <= n->ii_NumIndexAttrs);
n->ii_Unique = unique;
+ n->ii_NullsNotDistinct = nulls_not_distinct;
n->ii_ReadyForInserts = isready;
n->ii_CheckedUnchanged = false;
n->ii_IndexUnchanged = false;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 2b0236937a..6bdad462c7 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2775,6 +2775,7 @@ _outIndexStmt(StringInfo str, const IndexStmt *node)
WRITE_UINT_FIELD(oldCreateSubid);
WRITE_UINT_FIELD(oldFirstRelfilenodeSubid);
WRITE_BOOL_FIELD(unique);
+ WRITE_BOOL_FIELD(nulls_not_distinct);
WRITE_BOOL_FIELD(primary);
WRITE_BOOL_FIELD(isconstraint);
WRITE_BOOL_FIELD(deferrable);
@@ -3713,6 +3714,7 @@ _outConstraint(StringInfo str, const Constraint *node)
case CONSTR_UNIQUE:
appendStringInfoString(str, "UNIQUE");
+ WRITE_BOOL_FIELD(nulls_not_distinct);
WRITE_NODE_FIELD(keys);
WRITE_NODE_FIELD(including);
WRITE_NODE_FIELD(options);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b5966712ce..c4f3242506 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -625,6 +625,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
+%type <boolean> opt_unique_null_treatment
%type <ival> generated_when override_kind
%type <partspec> PartitionSpec OptPartitionSpec
%type <partelem> part_elem
@@ -3623,15 +3624,16 @@ ColConstraintElem:
n->location = @1;
$$ = (Node *)n;
}
- | UNIQUE opt_definition OptConsTableSpace
+ | UNIQUE opt_unique_null_treatment opt_definition OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
n->location = @1;
+ n->nulls_not_distinct = !$2;
n->keys = NULL;
- n->options = $2;
+ n->options = $3;
n->indexname = NULL;
- n->indexspace = $3;
+ n->indexspace = $4;
$$ = (Node *)n;
}
| PRIMARY KEY opt_definition OptConsTableSpace
@@ -3716,6 +3718,12 @@ ColConstraintElem:
}
;
+opt_unique_null_treatment:
+ NULLS_P DISTINCT { $$ = true; }
+ | NULLS_P NOT DISTINCT { $$ = false; }
+ | /*EMPTY*/ { $$ = true; }
+ ;
+
generated_when:
ALWAYS { $$ = ATTRIBUTE_IDENTITY_ALWAYS; }
| BY DEFAULT { $$ = ATTRIBUTE_IDENTITY_BY_DEFAULT; }
@@ -3828,18 +3836,19 @@ ConstraintElem:
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
}
- | UNIQUE '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
+ | UNIQUE opt_unique_null_treatment '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
ConstraintAttributeSpec
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
n->location = @1;
- n->keys = $3;
- n->including = $5;
- n->options = $6;
+ n->nulls_not_distinct = !$2;
+ n->keys = $4;
+ n->including = $6;
+ n->options = $7;
n->indexname = NULL;
- n->indexspace = $7;
- processCASbits($8, @8, "UNIQUE",
+ n->indexspace = $8;
+ processCASbits($9, @9, "UNIQUE",
&n->deferrable, &n->initdeferred, NULL,
NULL, yyscanner);
$$ = (Node *)n;
@@ -7411,7 +7420,7 @@ defacl_privilege_target:
IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
@@ -7421,9 +7430,10 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->accessMethod = $8;
n->indexParams = $10;
n->indexIncludingParams = $12;
- n->options = $13;
- n->tableSpace = $14;
- n->whereClause = $15;
+ n->nulls_not_distinct = !$13;
+ n->options = $14;
+ n->tableSpace = $15;
+ n->whereClause = $16;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -7441,7 +7451,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
}
| CREATE opt_unique INDEX opt_concurrently IF_P NOT EXISTS name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
@@ -7451,9 +7461,10 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->accessMethod = $11;
n->indexParams = $13;
n->indexIncludingParams = $15;
- n->options = $16;
- n->tableSpace = $17;
- n->whereClause = $18;
+ n->nulls_not_distinct = !$16;
+ n->options = $17;
+ n->tableSpace = $18;
+ n->whereClause = $19;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -13802,7 +13813,7 @@ a_expr: c_expr { $$ = $1; }
else
$$ = (Node *) makeA_Expr(AEXPR_OP_ALL, $2, $1, $5, @2);
}
- | UNIQUE select_with_parens
+ | UNIQUE opt_unique_null_treatment select_with_parens
{
/* Not sure how to get rid of the parentheses
* but there are lots of shift/reduce errors without them.
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 0eea214dd8..99efa26ce4 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;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 039b1d2b95..b16526e65e 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1444,6 +1444,9 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
{
appendStringInfoChar(&buf, ')');
+ if (idxrec->indnullsnotdistinct)
+ appendStringInfo(&buf, " NULLS NOT DISTINCT");
+
/*
* If it has options, append "WITH (options)"
*/
@@ -2312,9 +2315,20 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
/* Start off the constraint definition */
if (conForm->contype == CONSTRAINT_PRIMARY)
- appendStringInfoString(&buf, "PRIMARY KEY (");
+ appendStringInfoString(&buf, "PRIMARY KEY ");
else
- appendStringInfoString(&buf, "UNIQUE (");
+ appendStringInfoString(&buf, "UNIQUE ");
+
+ indexId = conForm->conindid;
+
+ indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId));
+ if (!HeapTupleIsValid(indtup))
+ elog(ERROR, "cache lookup failed for index %u", indexId);
+ if (conForm->contype == CONSTRAINT_UNIQUE &&
+ ((Form_pg_index) GETSTRUCT(indtup))->indnullsnotdistinct)
+ appendStringInfoString(&buf, "NULLS NOT DISTINCT ");
+
+ appendStringInfoString(&buf, "(");
/* Fetch and build target column list */
val = SysCacheGetAttr(CONSTROID, tup,
@@ -2327,12 +2341,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
appendStringInfoChar(&buf, ')');
- indexId = conForm->conindid;
-
/* Build including column list (from pg_index.indkeys) */
- indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId));
- if (!HeapTupleIsValid(indtup))
- elog(ERROR, "cache lookup failed for index %u", indexId);
val = SysCacheGetAttr(INDEXRELID, indtup,
Anum_pg_index_indnatts, &isnull);
if (isnull)
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 2e760e8a3b..2707fed12f 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2274,6 +2274,7 @@ RelationReloadIndexInfo(Relation relation)
* the array fields are allowed to change, though.
*/
relation->rd_index->indisunique = index->indisunique;
+ relation->rd_index->indnullsnotdistinct = index->indnullsnotdistinct;
relation->rd_index->indisprimary = index->indisprimary;
relation->rd_index->indisexclusion = index->indisexclusion;
relation->rd_index->indimmediate = index->indimmediate;
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index a3f22d7357..086e948fca 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -459,6 +459,7 @@ struct Tuplesortstate
/* These are specific to the index_btree subcase: */
bool enforceUnique; /* complain if we find duplicate tuples */
+ bool uniqueNullsNotDistinct; /* unique constraint null treatment */
/* These are specific to the index_hash subcase: */
uint32 high_mask; /* masks for sortable part of hash code */
@@ -1065,6 +1066,7 @@ Tuplesortstate *
tuplesort_begin_index_btree(Relation heapRel,
Relation indexRel,
bool enforceUnique,
+ bool uniqueNullsNotDistinct,
int workMem,
SortCoordinate coordinate,
bool randomAccess)
@@ -1103,6 +1105,7 @@ tuplesort_begin_index_btree(Relation heapRel,
state->heapRel = heapRel;
state->indexRel = indexRel;
state->enforceUnique = enforceUnique;
+ state->uniqueNullsNotDistinct = uniqueNullsNotDistinct;
indexScanKey = _bt_mkscankey(indexRel, NULL);
@@ -4200,14 +4203,15 @@ comparetup_index_btree(const SortTuple *a, const SortTuple *b,
/*
* If btree has asked us to enforce uniqueness, complain if two equal
- * tuples are detected (unless there was at least one NULL field).
+ * tuples are detected (unless there was at least one NULL field and NULLS
+ * NOT DISTINCT was not set).
*
* It is sufficient to make the test here, because if two tuples are equal
* they *must* get compared at some stage of the sort --- otherwise the
* sort algorithm wouldn't have checked whether one must appear before the
* other.
*/
- if (state->enforceUnique && !equal_hasnull)
+ if (state->enforceUnique && !(!state->uniqueNullsNotDistinct && equal_hasnull))
{
Datum values[INDEX_MAX_KEYS];
bool isnull[INDEX_MAX_KEYS];
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f1e8b0b5c2..340dc63406 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6474,6 +6474,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_indkey,
i_indisclustered,
i_indisreplident,
+ i_indnullsnotdistinct,
i_contype,
i_conname,
i_condeferrable,
@@ -6550,14 +6551,21 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"(SELECT pg_catalog.array_agg(attstattarget ORDER BY attnum) "
" FROM pg_catalog.pg_attribute "
" WHERE attrelid = i.indexrelid AND "
- " attstattarget >= 0) AS indstatvals ");
+ " attstattarget >= 0) AS indstatvals, ");
else
appendPQExpBuffer(query,
"0 AS parentidx, "
"i.indnatts AS indnkeyatts, "
"i.indnatts AS indnatts, "
"'' AS indstatcols, "
- "'' AS indstatvals ");
+ "'' AS indstatvals, ");
+
+ if (fout->remoteVersion >= 150000)
+ appendPQExpBuffer(query,
+ "i.indnullsnotdistinct ");
+ else
+ appendPQExpBuffer(query,
+ "false AS indnullsnotdistinct ");
/*
* The point of the messy-looking outer join is to find a constraint that
@@ -6620,6 +6628,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_indkey = PQfnumber(res, "indkey");
i_indisclustered = PQfnumber(res, "indisclustered");
i_indisreplident = PQfnumber(res, "indisreplident");
+ i_indnullsnotdistinct = PQfnumber(res, "indnullsnotdistinct");
i_contype = PQfnumber(res, "contype");
i_conname = PQfnumber(res, "conname");
i_condeferrable = PQfnumber(res, "condeferrable");
@@ -6696,6 +6705,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
indxinfo[j].indkeys, indxinfo[j].indnattrs);
indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
indxinfo[j].indisreplident = (PQgetvalue(res, j, i_indisreplident)[0] == 't');
+ indxinfo[j].indnullsnotdistinct = (PQgetvalue(res, j, i_indnullsnotdistinct)[0] == 't');
indxinfo[j].parentidx = atooid(PQgetvalue(res, j, i_parentidx));
indxinfo[j].partattaches = (SimplePtrList)
{
@@ -16112,8 +16122,11 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
}
else
{
- appendPQExpBuffer(q, "%s (",
+ appendPQExpBuffer(q, "%s",
coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
+ if (indxinfo->indnullsnotdistinct)
+ appendPQExpBuffer(q, " NULLS NOT DISTINCT");
+ appendPQExpBuffer(q, " (");
for (k = 0; k < indxinfo->indnkeyattrs; k++)
{
int indkey = (int) indxinfo->indkeys[k];
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 066a129ee5..9965ac2518 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -398,6 +398,7 @@ typedef struct _indxInfo
* contains both key and nonkey attributes */
bool indisclustered;
bool indisreplident;
+ bool indnullsnotdistinct;
Oid parentidx; /* if a partition, parent index OID */
SimplePtrList partattaches; /* if partitioned, partition attach objects */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 40433e32fa..c5b07ab8a1 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2181,6 +2181,11 @@ describeOneTableDetails(const char *schemaname,
else
appendPQExpBufferStr(&buf, "false AS indisreplident,\n");
+ if (pset.sversion >= 150000)
+ appendPQExpBufferStr(&buf, "i.indnullsnotdistinct,\n");
+ else
+ appendPQExpBufferStr(&buf, "false AS indnullsnotdistinct,\n");
+
appendPQExpBuffer(&buf, " a.amname, c2.relname, "
"pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
"FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
@@ -2205,14 +2210,20 @@ describeOneTableDetails(const char *schemaname,
char *deferrable = PQgetvalue(result, 0, 4);
char *deferred = PQgetvalue(result, 0, 5);
char *indisreplident = PQgetvalue(result, 0, 6);
- char *indamname = PQgetvalue(result, 0, 7);
- char *indtable = PQgetvalue(result, 0, 8);
- char *indpred = PQgetvalue(result, 0, 9);
+ char *indnullsnotdistinct = PQgetvalue(result, 0, 7);
+ char *indamname = PQgetvalue(result, 0, 8);
+ char *indtable = PQgetvalue(result, 0, 9);
+ char *indpred = PQgetvalue(result, 0, 10);
if (strcmp(indisprimary, "t") == 0)
printfPQExpBuffer(&tmpbuf, _("primary key, "));
else if (strcmp(indisunique, "t") == 0)
- printfPQExpBuffer(&tmpbuf, _("unique, "));
+ {
+ printfPQExpBuffer(&tmpbuf, _("unique"));
+ if (strcmp(indnullsnotdistinct, "t") == 0)
+ appendPQExpBufferStr(&tmpbuf, _(" nulls not distinct"));
+ appendPQExpBuffer(&tmpbuf, _(", "));
+ }
else
resetPQExpBuffer(&tmpbuf);
appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index c31111495f..f853846ee1 100644
--- a/src/include/catalog/pg_index.h
+++ b/src/include/catalog/pg_index.h
@@ -34,6 +34,7 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO
int16 indnatts; /* total number of columns in index */
int16 indnkeyatts; /* number of key columns in index */
bool indisunique; /* is this a unique index? */
+ bool indnullsnotdistinct; /* null treatment in unique index */
bool indisprimary; /* is this index for primary key? */
bool indisexclusion; /* is this index for exclusion constraint? */
bool indimmediate; /* is uniqueness enforced immediately? */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 4ea8735dd8..dd95dc40c7 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -173,6 +173,7 @@ typedef struct IndexInfo
uint16 *ii_UniqueStrats; /* array with one entry per column */
Datum *ii_OpclassOptions; /* array with one entry per column */
bool ii_Unique;
+ bool ii_NullsNotDistinct;
bool ii_ReadyForInserts;
bool ii_CheckedUnchanged;
bool ii_IndexUnchanged;
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index fe173101d1..50de4c62af 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -96,7 +96,7 @@ extern List *make_ands_implicit(Expr *clause);
extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid,
List *expressions, List *predicates,
- bool unique, bool isready, bool concurrent);
+ bool unique, bool nulls_not_distinct, bool isready, bool concurrent);
extern DefElem *makeDefElem(char *name, Node *arg, int location);
extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 3e9bdc781f..37fcc4c9b5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2277,6 +2277,7 @@ typedef struct Constraint
char generated_when; /* ALWAYS or BY DEFAULT */
/* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */
+ bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
List *keys; /* String nodes naming referenced key
* column(s) */
List *including; /* String nodes naming referenced nonkey
@@ -2905,6 +2906,7 @@ typedef struct IndexStmt
SubTransactionId oldFirstRelfilenodeSubid; /* rd_firstRelfilenodeSubid of
* oldNode */
bool unique; /* is index unique? */
+ bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
bool primary; /* is index a primary key? */
bool isconstraint; /* is it for a pkey/unique constraint? */
bool deferrable; /* is the constraint DEFERRABLE? */
diff --git a/src/include/utils/tuplesort.h b/src/include/utils/tuplesort.h
index 37f79de8c6..da5ba59198 100644
--- a/src/include/utils/tuplesort.h
+++ b/src/include/utils/tuplesort.h
@@ -208,6 +208,7 @@ extern Tuplesortstate *tuplesort_begin_cluster(TupleDesc tupDesc,
extern Tuplesortstate *tuplesort_begin_index_btree(Relation heapRel,
Relation indexRel,
bool enforceUnique,
+ bool uniqueNullsNotDistinct,
int workMem, SortCoordinate coordinate,
bool randomAccess);
extern Tuplesortstate *tuplesort_begin_index_hash(Relation heapRel,
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e32cf8bb57..36ccbb5f15 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -445,6 +445,29 @@ SELECT * FROM UNIQUE_TBL;
6 | six-upsert-insert
(7 rows)
+DROP TABLE UNIQUE_TBL;
+CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+INSERT INTO UNIQUE_TBL VALUES (2, 'two');
+INSERT INTO UNIQUE_TBL VALUES (1, 'three');
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(1) already exists.
+INSERT INTO UNIQUE_TBL VALUES (4, 'four');
+INSERT INTO UNIQUE_TBL VALUES (5, 'one');
+INSERT INTO UNIQUE_TBL (t) VALUES ('six');
+INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(null) already exists.
+SELECT * FROM UNIQUE_TBL;
+ i | t
+---+------
+ 1 | one
+ 2 | two
+ 4 | four
+ 5 | one
+ | six
+(5 rows)
+
DROP TABLE UNIQUE_TBL;
CREATE TABLE UNIQUE_TBL (i int, t text,
UNIQUE(i,t));
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index daf75dd5c4..53c8e830ce 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1272,6 +1272,67 @@ SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
DROP INDEX hash_tuplesort_idx;
RESET maintenance_work_mem;
--
+-- Test unique null behavior
+--
+CREATE TABLE unique_tbl (i int, t text);
+CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
+CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;
+INSERT INTO unique_tbl VALUES (1, 'one');
+INSERT INTO unique_tbl VALUES (2, 'two');
+INSERT INTO unique_tbl VALUES (3, 'three');
+INSERT INTO unique_tbl VALUES (4, 'four');
+INSERT INTO unique_tbl VALUES (5, 'one');
+INSERT INTO unique_tbl (t) VALUES ('six');
+INSERT INTO unique_tbl (t) VALUES ('seven'); -- error from unique_idx2
+ERROR: duplicate key value violates unique constraint "unique_idx2"
+DETAIL: Key (i)=(null) already exists.
+DROP INDEX unique_idx1, unique_idx2;
+INSERT INTO unique_tbl (t) VALUES ('seven');
+-- build indexes on filled table
+CREATE UNIQUE INDEX unique_idx3 ON unique_tbl (i) NULLS DISTINCT; -- ok
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- error
+ERROR: could not create unique index "unique_idx4"
+DETAIL: Key (i)=(null) is duplicated.
+DELETE FROM unique_tbl WHERE t = 'seven';
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- ok now
+\d unique_tbl
+ Table "public.unique_tbl"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ i | integer | | |
+ t | text | | |
+Indexes:
+ "unique_idx3" UNIQUE, btree (i)
+ "unique_idx4" UNIQUE, btree (i) NULLS NOT DISTINCT
+
+\d unique_idx3
+ Index "public.unique_idx3"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ i | integer | yes | i
+unique, btree, for table "public.unique_tbl"
+
+\d unique_idx4
+ Index "public.unique_idx4"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ i | integer | yes | i
+unique nulls not distinct, btree, for table "public.unique_tbl"
+
+SELECT pg_get_indexdef('unique_idx3'::regclass);
+ pg_get_indexdef
+----------------------------------------------------------------------
+ CREATE UNIQUE INDEX unique_idx3 ON public.unique_tbl USING btree (i)
+(1 row)
+
+SELECT pg_get_indexdef('unique_idx4'::regclass);
+ pg_get_indexdef
+-----------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX unique_idx4 ON public.unique_tbl USING btree (i) NULLS NOT DISTINCT
+(1 row)
+
+DROP TABLE unique_tbl;
+--
-- Test functional index
--
CREATE TABLE func_index_heap (f1 text, f2 text);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 458f805778..34de0c969a 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -306,6 +306,20 @@ CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text);
DROP TABLE UNIQUE_TBL;
+CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
+
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+INSERT INTO UNIQUE_TBL VALUES (2, 'two');
+INSERT INTO UNIQUE_TBL VALUES (1, 'three');
+INSERT INTO UNIQUE_TBL VALUES (4, 'four');
+INSERT INTO UNIQUE_TBL VALUES (5, 'one');
+INSERT INTO UNIQUE_TBL (t) VALUES ('six');
+INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
+
+SELECT * FROM UNIQUE_TBL;
+
+DROP TABLE UNIQUE_TBL;
+
CREATE TABLE UNIQUE_TBL (i int, t text,
UNIQUE(i,t));
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 8b353be16e..9003950a1f 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -387,6 +387,43 @@ CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fi
RESET maintenance_work_mem;
+--
+-- Test unique null behavior
+--
+CREATE TABLE unique_tbl (i int, t text);
+
+CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
+CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;
+
+INSERT INTO unique_tbl VALUES (1, 'one');
+INSERT INTO unique_tbl VALUES (2, 'two');
+INSERT INTO unique_tbl VALUES (3, 'three');
+INSERT INTO unique_tbl VALUES (4, 'four');
+INSERT INTO unique_tbl VALUES (5, 'one');
+INSERT INTO unique_tbl (t) VALUES ('six');
+INSERT INTO unique_tbl (t) VALUES ('seven'); -- error from unique_idx2
+
+DROP INDEX unique_idx1, unique_idx2;
+
+INSERT INTO unique_tbl (t) VALUES ('seven');
+
+-- build indexes on filled table
+CREATE UNIQUE INDEX unique_idx3 ON unique_tbl (i) NULLS DISTINCT; -- ok
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- error
+
+DELETE FROM unique_tbl WHERE t = 'seven';
+
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- ok now
+
+\d unique_tbl
+\d unique_idx3
+\d unique_idx4
+SELECT pg_get_indexdef('unique_idx3'::regclass);
+SELECT pg_get_indexdef('unique_idx4'::regclass);
+
+DROP TABLE unique_tbl;
+
+
--
-- Test functional index
--
base-commit: f032f63e727c1ab07603b3d1cd88d50f850d5738
--
2.34.1
Re: UNIQUE null treatment option
Since cfbot did failed with error, probably, unrelated to the patch itself
(see https://cirrus-ci.com/task/5330150500859904)
and repeated check did not start automatically, I reattach patch v3 to
restart cfbot on this patch.
--
Best regards,
Maxim Orlov.
Attachments:
v3-0001-Add-UNIQUE-null-treatment-option.patchapplication/octet-stream; name=v3-0001-Add-UNIQUE-null-treatment-option.patchDownload
From c07b757ff5a31427de07e394f7c4736cb0a05378 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter@eisentraut.org>
Date: Mon, 24 Jan 2022 16:47:25 +0100
Subject: [PATCH v3] 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.
XXX 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. But perhaps the double
negatives make some code harder to read.
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 ++--
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/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 +++++++++++++
35 files changed, 347 insertions(+), 56 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 1e65c426b2..2acd6ce98c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -4265,6 +4265,19 @@ <title><structname>pg_index</structname> Columns</title>
</para></entry>
</row>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>indnullsnotdistinct</structfield> <type>bool</type>
+ </para>
+ <para>
+ 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).
+ </para></entry>
+ </row>
+
<row>
<entry role="catalog_table_entry"><para role="column_definition">
<structfield>indisprimary</structfield> <type>bool</type>
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 22f6c5c7ab..c10de43492 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -759,14 +759,33 @@ <title>Unique Constraints</title>
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 <literal>NULLS
+ NOT DISTINCT</literal>, like
+<programlisting>
+CREATE TABLE products (
+ product_no integer UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis>,
+ name text,
+ price numeric
+);
+</programlisting>
+ or
+<programlisting>
+CREATE TABLE products (
+ product_no integer,
+ name text,
+ price numeric,
+ UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis> (product_no)
+);
+</programlisting>
+ The default behavior can be specified explicitly using <literal>NULLS
+ DISTINCT</literal>. 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.
</para>
</sect2>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml
index c5e68c175f..350c75bc31 100644
--- a/doc/src/sgml/information_schema.sgml
+++ b/doc/src/sgml/information_schema.sgml
@@ -6899,6 +6899,18 @@ <title><structname>table_constraints</structname> Columns</title>
<literal>YES</literal>)
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>nulls_distinct</structfield> <type>yes_or_no</type>
+ </para>
+ <para>
+ If the constraint is a unique constraint, then <literal>YES</literal>
+ if the constraint treats nulls as distinct or <literal>NO</literal> if
+ it treats nulls as not distinct, otherwise null for other types of
+ constraints.
+ </para></entry>
+ </row>
</tbody>
</tgroup>
</table>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index a76e2e7322..b20f6c651c 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -103,7 +103,7 @@
DEFAULT <replaceable>default_expr</replaceable> |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
- UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
@@ -113,7 +113,7 @@
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
diff --git a/doc/src/sgml/ref/create_index.sgml b/doc/src/sgml/ref/create_index.sgml
index 89a4d746f6..91eaaabc90 100644
--- a/doc/src/sgml/ref/create_index.sgml
+++ b/doc/src/sgml/ref/create_index.sgml
@@ -24,6 +24,7 @@
CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class="parameter">name</replaceable> ] ON [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ USING <replaceable class="parameter">method</replaceable> ]
( { <replaceable class="parameter">column_name</replaceable> | ( <replaceable class="parameter">expression</replaceable> ) } [ COLLATE <replaceable class="parameter">collation</replaceable> ] [ <replaceable class="parameter">opclass</replaceable> [ ( <replaceable class="parameter">opclass_parameter</replaceable> = <replaceable class="parameter">value</replaceable> [, ... ] ) ] ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] )
[ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
+ [ NULLS [ NOT ] DISTINCT ]
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
@@ -334,6 +335,18 @@ <title>Parameters</title>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>NULLS DISTINCT</literal></term>
+ <term><literal>NULLS NOT DISTINCT</literal></term>
+ <listitem>
+ <para>
+ 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.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><replaceable class="parameter">storage_parameter</replaceable></term>
<listitem>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index b97bb9ded1..7e4ef312c0 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -67,7 +67,7 @@
DEFAULT <replaceable>default_expr</replaceable> |
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
- UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
@@ -77,7 +77,7 @@
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
- UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
+ UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] |
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
@@ -917,8 +917,8 @@ <title>Parameters</title>
</varlistentry>
<varlistentry>
- <term><literal>UNIQUE</literal> (column constraint)</term>
- <term><literal>UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
+ <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ]</literal> (column constraint)</term>
+ <term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
<optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
<listitem>
@@ -934,7 +934,8 @@ <title>Parameters</title>
<para>
For the purpose of a unique constraint, null values are not
- considered equal.
+ considered equal, unless <literal>NULLS NOT DISTINCT</literal> is
+ specified.
</para>
<para>
diff --git a/src/backend/access/nbtree/nbtinsert.c b/src/backend/access/nbtree/nbtinsert.c
index 62746c4721..68628ec000 100644
--- a/src/backend/access/nbtree/nbtinsert.c
+++ b/src/backend/access/nbtree/nbtinsert.c
@@ -398,9 +398,9 @@ _bt_search_insert(Relation rel, BTInsertState insertstate)
* _bt_findinsertloc() to reuse most of the binary search work we do
* here.
*
- * Do not call here when there are NULL values in scan key. NULL should be
- * considered unequal to NULL when checking for duplicates, but we are not
- * prepared to handle that correctly.
+ * This code treats NULLs as equal, unlike the default semantics for unique
+ * indexes. So do not call here when there are NULL values in scan key and
+ * the index uses the default NULLS DISTINCT mode.
*/
static TransactionId
_bt_check_unique(Relation rel, BTInsertState insertstate, Relation heapRel,
diff --git a/src/backend/access/nbtree/nbtsort.c b/src/backend/access/nbtree/nbtsort.c
index dc220146fd..8a19de2f66 100644
--- a/src/backend/access/nbtree/nbtsort.c
+++ b/src/backend/access/nbtree/nbtsort.c
@@ -89,6 +89,7 @@ typedef struct BTSpool
Relation heap;
Relation index;
bool isunique;
+ bool nulls_not_distinct;
} BTSpool;
/*
@@ -106,6 +107,7 @@ typedef struct BTShared
Oid heaprelid;
Oid indexrelid;
bool isunique;
+ bool nulls_not_distinct;
bool isconcurrent;
int scantuplesortstates;
@@ -206,6 +208,7 @@ typedef struct BTLeader
typedef struct BTBuildState
{
bool isunique;
+ bool nulls_not_distinct;
bool havedead;
Relation heap;
BTSpool *spool;
@@ -307,6 +310,7 @@ btbuild(Relation heap, Relation index, IndexInfo *indexInfo)
#endif /* BTREE_BUILD_STATS */
buildstate.isunique = indexInfo->ii_Unique;
+ buildstate.nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
buildstate.havedead = false;
buildstate.heap = heap;
buildstate.spool = NULL;
@@ -380,6 +384,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
btspool->heap = heap;
btspool->index = index;
btspool->isunique = indexInfo->ii_Unique;
+ btspool->nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
/* Save as primary spool */
buildstate->spool = btspool;
@@ -429,6 +434,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
*/
buildstate->spool->sortstate =
tuplesort_begin_index_btree(heap, index, buildstate->isunique,
+ buildstate->nulls_not_distinct,
maintenance_work_mem, coordinate,
false);
@@ -468,7 +474,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
* full, so we give it only work_mem
*/
buildstate->spool2->sortstate =
- tuplesort_begin_index_btree(heap, index, false, work_mem,
+ tuplesort_begin_index_btree(heap, index, false, false, work_mem,
coordinate2, false);
}
@@ -1554,6 +1560,7 @@ _bt_begin_parallel(BTBuildState *buildstate, bool isconcurrent, int request)
btshared->heaprelid = RelationGetRelid(btspool->heap);
btshared->indexrelid = RelationGetRelid(btspool->index);
btshared->isunique = btspool->isunique;
+ btshared->nulls_not_distinct = btspool->nulls_not_distinct;
btshared->isconcurrent = isconcurrent;
btshared->scantuplesortstates = scantuplesortstates;
ConditionVariableInit(&btshared->workersdonecv);
@@ -1747,6 +1754,7 @@ _bt_leader_participate_as_worker(BTBuildState *buildstate)
leaderworker->heap = buildstate->spool->heap;
leaderworker->index = buildstate->spool->index;
leaderworker->isunique = buildstate->spool->isunique;
+ leaderworker->nulls_not_distinct = buildstate->spool->nulls_not_distinct;
/* Initialize second spool, if required */
if (!btleader->btshared->isunique)
@@ -1846,6 +1854,7 @@ _bt_parallel_build_main(dsm_segment *seg, shm_toc *toc)
btspool->heap = heapRel;
btspool->index = indexRel;
btspool->isunique = btshared->isunique;
+ btspool->nulls_not_distinct = btshared->nulls_not_distinct;
/* Look up shared state private to tuplesort.c */
sharedsort = shm_toc_lookup(toc, PARALLEL_KEY_TUPLESORT, false);
@@ -1928,6 +1937,7 @@ _bt_parallel_scan_and_sort(BTSpool *btspool, BTSpool *btspool2,
btspool->sortstate = tuplesort_begin_index_btree(btspool->heap,
btspool->index,
btspool->isunique,
+ btspool->nulls_not_distinct,
sortmem, coordinate,
false);
@@ -1950,13 +1960,14 @@ _bt_parallel_scan_and_sort(BTSpool *btspool, BTSpool *btspool2,
coordinate2->nParticipants = -1;
coordinate2->sharedsort = sharedsort2;
btspool2->sortstate =
- tuplesort_begin_index_btree(btspool->heap, btspool->index, false,
+ tuplesort_begin_index_btree(btspool->heap, btspool->index, false, false,
Min(sortmem, work_mem), coordinate2,
false);
}
/* Fill in buildstate for _bt_build_callback() */
buildstate.isunique = btshared->isunique;
+ buildstate.nulls_not_distinct = btshared->nulls_not_distinct;
buildstate.havedead = false;
buildstate.heap = btspool->heap;
buildstate.spool = btspool;
diff --git a/src/backend/access/nbtree/nbtutils.c b/src/backend/access/nbtree/nbtutils.c
index ed67863c56..6a651d8397 100644
--- a/src/backend/access/nbtree/nbtutils.c
+++ b/src/backend/access/nbtree/nbtutils.c
@@ -165,6 +165,13 @@ _bt_mkscankey(Relation rel, IndexTuple itup)
key->anynullkeys = true;
}
+ /*
+ * In NULLS NOT DISTINCT mode, we pretend that there are no null keys, so
+ * that full uniqueness check is done.
+ */
+ if (rel->rd_index->indnullsnotdistinct)
+ key->anynullkeys = false;
+
return key;
}
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 2308d40256..5e3fc2b35d 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -614,6 +614,7 @@ UpdateIndexRelation(Oid indexoid,
values[Anum_pg_index_indnatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexAttrs);
values[Anum_pg_index_indnkeyatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexKeyAttrs);
values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique);
+ values[Anum_pg_index_indnullsnotdistinct - 1] = BoolGetDatum(indexInfo->ii_NullsNotDistinct);
values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary);
values[Anum_pg_index_indisexclusion - 1] = BoolGetDatum(isexclusion);
values[Anum_pg_index_indimmediate - 1] = BoolGetDatum(immediate);
@@ -1368,6 +1369,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
indexExprs,
indexPreds,
oldInfo->ii_Unique,
+ oldInfo->ii_NullsNotDistinct,
false, /* not ready for inserts */
true);
@@ -2440,6 +2442,7 @@ BuildIndexInfo(Relation index)
RelationGetIndexExpressions(index),
RelationGetIndexPredicate(index),
indexStruct->indisunique,
+ indexStruct->indnullsnotdistinct,
indexStruct->indisready,
false);
@@ -2499,6 +2502,7 @@ BuildDummyIndexInfo(Relation index)
RelationGetDummyIndexExpressions(index),
NIL,
indexStruct->indisunique,
+ indexStruct->indnullsnotdistinct,
indexStruct->indisready,
false);
@@ -2532,6 +2536,9 @@ CompareIndexInfo(IndexInfo *info1, IndexInfo *info2,
if (info1->ii_Unique != info2->ii_Unique)
return false;
+ if (info1->ii_NullsNotDistinct != info2->ii_NullsNotDistinct)
+ return false;
+
/* indexes are only equivalent if they have the same access method */
if (info1->ii_Am != info2->ii_Am)
return false;
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index b4f348a24d..c4ef8e78a5 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -1838,7 +1838,11 @@ CREATE VIEW table_constraints AS
AS is_deferrable,
CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
AS initially_deferred,
- CAST('YES' AS yes_or_no) AS enforced
+ CAST('YES' AS yes_or_no) AS enforced,
+ CAST(CASE WHEN c.contype = 'u'
+ THEN CASE WHEN (SELECT NOT indnullsnotdistinct FROM pg_index WHERE indexrelid = conindid) THEN 'YES' ELSE 'NO' END
+ END
+ AS yes_or_no) AS nulls_distinct
FROM pg_namespace nc,
pg_namespace nr,
@@ -1868,7 +1872,8 @@ CREATE VIEW table_constraints AS
CAST('CHECK' AS character_data) AS constraint_type,
CAST('NO' AS yes_or_no) AS is_deferrable,
CAST('NO' AS yes_or_no) AS initially_deferred,
- CAST('YES' AS yes_or_no) AS enforced
+ CAST('YES' AS yes_or_no) AS enforced,
+ CAST(NULL AS yes_or_no) AS nulls_distinct
FROM pg_namespace nr,
pg_class r,
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index b8a78f4d41..097d9c4784 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -228,6 +228,7 @@ F263 Comma-separated predicates in simple CASE expression NO
F271 Compound character literals YES
F281 LIKE enhancements YES
F291 UNIQUE predicate NO
+F292 UNIQUE null treatment YES SQL:202x draft
F301 CORRESPONDING in query expressions NO
F302 INTERSECT table operator YES
F302 INTERSECT table operator 01 INTERSECT DISTINCT table operator YES
diff --git a/src/backend/catalog/toasting.c b/src/backend/catalog/toasting.c
index 3c27cb1e71..9bc10729b0 100644
--- a/src/backend/catalog/toasting.c
+++ b/src/backend/catalog/toasting.c
@@ -301,6 +301,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
indexInfo->ii_ExclusionStrats = NULL;
indexInfo->ii_OpclassOptions = NULL;
indexInfo->ii_Unique = true;
+ indexInfo->ii_NullsNotDistinct = false;
indexInfo->ii_ReadyForInserts = true;
indexInfo->ii_CheckedUnchanged = false;
indexInfo->ii_IndexUnchanged = false;
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index e5cf1bde13..c9dc92f380 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -226,7 +226,7 @@ CheckIndexCompatible(Oid oldId,
* ii_NumIndexKeyAttrs with same value.
*/
indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes,
- accessMethodId, NIL, NIL, false, false, false);
+ accessMethodId, NIL, NIL, false, false, false, false);
typeObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
collationObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
@@ -867,6 +867,7 @@ DefineIndex(Oid relationId,
NIL, /* expressions, NIL for now */
make_ands_implicit((Expr *) stmt->whereClause),
stmt->unique,
+ stmt->nulls_not_distinct,
!concurrent,
concurrent);
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 90b5da51c9..6bd95bbce2 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -3072,6 +3072,7 @@ _copyConstraint(const Constraint *from)
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
COPY_SCALAR_FIELD(generated_when);
+ COPY_SCALAR_FIELD(nulls_not_distinct);
COPY_NODE_FIELD(keys);
COPY_NODE_FIELD(including);
COPY_NODE_FIELD(exclusions);
@@ -3664,6 +3665,7 @@ _copyIndexStmt(const IndexStmt *from)
COPY_SCALAR_FIELD(oldCreateSubid);
COPY_SCALAR_FIELD(oldFirstRelfilenodeSubid);
COPY_SCALAR_FIELD(unique);
+ COPY_SCALAR_FIELD(nulls_not_distinct);
COPY_SCALAR_FIELD(primary);
COPY_SCALAR_FIELD(isconstraint);
COPY_SCALAR_FIELD(deferrable);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 06345da3ba..4126516222 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -1400,6 +1400,7 @@ _equalIndexStmt(const IndexStmt *a, const IndexStmt *b)
COMPARE_SCALAR_FIELD(oldCreateSubid);
COMPARE_SCALAR_FIELD(oldFirstRelfilenodeSubid);
COMPARE_SCALAR_FIELD(unique);
+ COMPARE_SCALAR_FIELD(nulls_not_distinct);
COMPARE_SCALAR_FIELD(primary);
COMPARE_SCALAR_FIELD(isconstraint);
COMPARE_SCALAR_FIELD(deferrable);
@@ -2710,6 +2711,7 @@ _equalConstraint(const Constraint *a, const Constraint *b)
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
COMPARE_SCALAR_FIELD(generated_when);
+ COMPARE_SCALAR_FIELD(nulls_not_distinct);
COMPARE_NODE_FIELD(keys);
COMPARE_NODE_FIELD(including);
COMPARE_NODE_FIELD(exclusions);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index 822395625b..c85d8fe975 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -741,7 +741,7 @@ make_ands_implicit(Expr *clause)
*/
IndexInfo *
makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
- List *predicates, bool unique, bool isready, bool concurrent)
+ List *predicates, bool unique, bool nulls_not_distinct, bool isready, bool concurrent)
{
IndexInfo *n = makeNode(IndexInfo);
@@ -750,6 +750,7 @@ makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid, List *expressions,
Assert(n->ii_NumIndexKeyAttrs != 0);
Assert(n->ii_NumIndexKeyAttrs <= n->ii_NumIndexAttrs);
n->ii_Unique = unique;
+ n->ii_NullsNotDistinct = nulls_not_distinct;
n->ii_ReadyForInserts = isready;
n->ii_CheckedUnchanged = false;
n->ii_IndexUnchanged = false;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 2b0236937a..6bdad462c7 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2775,6 +2775,7 @@ _outIndexStmt(StringInfo str, const IndexStmt *node)
WRITE_UINT_FIELD(oldCreateSubid);
WRITE_UINT_FIELD(oldFirstRelfilenodeSubid);
WRITE_BOOL_FIELD(unique);
+ WRITE_BOOL_FIELD(nulls_not_distinct);
WRITE_BOOL_FIELD(primary);
WRITE_BOOL_FIELD(isconstraint);
WRITE_BOOL_FIELD(deferrable);
@@ -3713,6 +3714,7 @@ _outConstraint(StringInfo str, const Constraint *node)
case CONSTR_UNIQUE:
appendStringInfoString(str, "UNIQUE");
+ WRITE_BOOL_FIELD(nulls_not_distinct);
WRITE_NODE_FIELD(keys);
WRITE_NODE_FIELD(including);
WRITE_NODE_FIELD(options);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index b5966712ce..c4f3242506 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -625,6 +625,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <ival> opt_window_exclusion_clause
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
+%type <boolean> opt_unique_null_treatment
%type <ival> generated_when override_kind
%type <partspec> PartitionSpec OptPartitionSpec
%type <partelem> part_elem
@@ -3623,15 +3624,16 @@ ColConstraintElem:
n->location = @1;
$$ = (Node *)n;
}
- | UNIQUE opt_definition OptConsTableSpace
+ | UNIQUE opt_unique_null_treatment opt_definition OptConsTableSpace
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
n->location = @1;
+ n->nulls_not_distinct = !$2;
n->keys = NULL;
- n->options = $2;
+ n->options = $3;
n->indexname = NULL;
- n->indexspace = $3;
+ n->indexspace = $4;
$$ = (Node *)n;
}
| PRIMARY KEY opt_definition OptConsTableSpace
@@ -3716,6 +3718,12 @@ ColConstraintElem:
}
;
+opt_unique_null_treatment:
+ NULLS_P DISTINCT { $$ = true; }
+ | NULLS_P NOT DISTINCT { $$ = false; }
+ | /*EMPTY*/ { $$ = true; }
+ ;
+
generated_when:
ALWAYS { $$ = ATTRIBUTE_IDENTITY_ALWAYS; }
| BY DEFAULT { $$ = ATTRIBUTE_IDENTITY_BY_DEFAULT; }
@@ -3828,18 +3836,19 @@ ConstraintElem:
n->initially_valid = !n->skip_validation;
$$ = (Node *)n;
}
- | UNIQUE '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
+ | UNIQUE opt_unique_null_treatment '(' columnList ')' opt_c_include opt_definition OptConsTableSpace
ConstraintAttributeSpec
{
Constraint *n = makeNode(Constraint);
n->contype = CONSTR_UNIQUE;
n->location = @1;
- n->keys = $3;
- n->including = $5;
- n->options = $6;
+ n->nulls_not_distinct = !$2;
+ n->keys = $4;
+ n->including = $6;
+ n->options = $7;
n->indexname = NULL;
- n->indexspace = $7;
- processCASbits($8, @8, "UNIQUE",
+ n->indexspace = $8;
+ processCASbits($9, @9, "UNIQUE",
&n->deferrable, &n->initdeferred, NULL,
NULL, yyscanner);
$$ = (Node *)n;
@@ -7411,7 +7420,7 @@ defacl_privilege_target:
IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
@@ -7421,9 +7430,10 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->accessMethod = $8;
n->indexParams = $10;
n->indexIncludingParams = $12;
- n->options = $13;
- n->tableSpace = $14;
- n->whereClause = $15;
+ n->nulls_not_distinct = !$13;
+ n->options = $14;
+ n->tableSpace = $15;
+ n->whereClause = $16;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -7441,7 +7451,7 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
}
| CREATE opt_unique INDEX opt_concurrently IF_P NOT EXISTS name
ON relation_expr access_method_clause '(' index_params ')'
- opt_include opt_reloptions OptTableSpace where_clause
+ opt_include opt_unique_null_treatment opt_reloptions OptTableSpace where_clause
{
IndexStmt *n = makeNode(IndexStmt);
n->unique = $2;
@@ -7451,9 +7461,10 @@ IndexStmt: CREATE opt_unique INDEX opt_concurrently opt_index_name
n->accessMethod = $11;
n->indexParams = $13;
n->indexIncludingParams = $15;
- n->options = $16;
- n->tableSpace = $17;
- n->whereClause = $18;
+ n->nulls_not_distinct = !$16;
+ n->options = $17;
+ n->tableSpace = $18;
+ n->whereClause = $19;
n->excludeOpNames = NIL;
n->idxcomment = NULL;
n->indexOid = InvalidOid;
@@ -13802,7 +13813,7 @@ a_expr: c_expr { $$ = $1; }
else
$$ = (Node *) makeA_Expr(AEXPR_OP_ALL, $2, $1, $5, @2);
}
- | UNIQUE select_with_parens
+ | UNIQUE opt_unique_null_treatment select_with_parens
{
/* Not sure how to get rid of the parentheses
* but there are lots of shift/reduce errors without them.
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 0eea214dd8..99efa26ce4 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;
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 039b1d2b95..b16526e65e 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -1444,6 +1444,9 @@ pg_get_indexdef_worker(Oid indexrelid, int colno,
{
appendStringInfoChar(&buf, ')');
+ if (idxrec->indnullsnotdistinct)
+ appendStringInfo(&buf, " NULLS NOT DISTINCT");
+
/*
* If it has options, append "WITH (options)"
*/
@@ -2312,9 +2315,20 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
/* Start off the constraint definition */
if (conForm->contype == CONSTRAINT_PRIMARY)
- appendStringInfoString(&buf, "PRIMARY KEY (");
+ appendStringInfoString(&buf, "PRIMARY KEY ");
else
- appendStringInfoString(&buf, "UNIQUE (");
+ appendStringInfoString(&buf, "UNIQUE ");
+
+ indexId = conForm->conindid;
+
+ indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId));
+ if (!HeapTupleIsValid(indtup))
+ elog(ERROR, "cache lookup failed for index %u", indexId);
+ if (conForm->contype == CONSTRAINT_UNIQUE &&
+ ((Form_pg_index) GETSTRUCT(indtup))->indnullsnotdistinct)
+ appendStringInfoString(&buf, "NULLS NOT DISTINCT ");
+
+ appendStringInfoString(&buf, "(");
/* Fetch and build target column list */
val = SysCacheGetAttr(CONSTROID, tup,
@@ -2327,12 +2341,7 @@ pg_get_constraintdef_worker(Oid constraintId, bool fullCommand,
appendStringInfoChar(&buf, ')');
- indexId = conForm->conindid;
-
/* Build including column list (from pg_index.indkeys) */
- indtup = SearchSysCache1(INDEXRELID, ObjectIdGetDatum(indexId));
- if (!HeapTupleIsValid(indtup))
- elog(ERROR, "cache lookup failed for index %u", indexId);
val = SysCacheGetAttr(INDEXRELID, indtup,
Anum_pg_index_indnatts, &isnull);
if (isnull)
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 2e760e8a3b..2707fed12f 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2274,6 +2274,7 @@ RelationReloadIndexInfo(Relation relation)
* the array fields are allowed to change, though.
*/
relation->rd_index->indisunique = index->indisunique;
+ relation->rd_index->indnullsnotdistinct = index->indnullsnotdistinct;
relation->rd_index->indisprimary = index->indisprimary;
relation->rd_index->indisexclusion = index->indisexclusion;
relation->rd_index->indimmediate = index->indimmediate;
diff --git a/src/backend/utils/sort/tuplesort.c b/src/backend/utils/sort/tuplesort.c
index a3f22d7357..086e948fca 100644
--- a/src/backend/utils/sort/tuplesort.c
+++ b/src/backend/utils/sort/tuplesort.c
@@ -459,6 +459,7 @@ struct Tuplesortstate
/* These are specific to the index_btree subcase: */
bool enforceUnique; /* complain if we find duplicate tuples */
+ bool uniqueNullsNotDistinct; /* unique constraint null treatment */
/* These are specific to the index_hash subcase: */
uint32 high_mask; /* masks for sortable part of hash code */
@@ -1065,6 +1066,7 @@ Tuplesortstate *
tuplesort_begin_index_btree(Relation heapRel,
Relation indexRel,
bool enforceUnique,
+ bool uniqueNullsNotDistinct,
int workMem,
SortCoordinate coordinate,
bool randomAccess)
@@ -1103,6 +1105,7 @@ tuplesort_begin_index_btree(Relation heapRel,
state->heapRel = heapRel;
state->indexRel = indexRel;
state->enforceUnique = enforceUnique;
+ state->uniqueNullsNotDistinct = uniqueNullsNotDistinct;
indexScanKey = _bt_mkscankey(indexRel, NULL);
@@ -4200,14 +4203,15 @@ comparetup_index_btree(const SortTuple *a, const SortTuple *b,
/*
* If btree has asked us to enforce uniqueness, complain if two equal
- * tuples are detected (unless there was at least one NULL field).
+ * tuples are detected (unless there was at least one NULL field and NULLS
+ * NOT DISTINCT was not set).
*
* It is sufficient to make the test here, because if two tuples are equal
* they *must* get compared at some stage of the sort --- otherwise the
* sort algorithm wouldn't have checked whether one must appear before the
* other.
*/
- if (state->enforceUnique && !equal_hasnull)
+ if (state->enforceUnique && !(!state->uniqueNullsNotDistinct && equal_hasnull))
{
Datum values[INDEX_MAX_KEYS];
bool isnull[INDEX_MAX_KEYS];
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index f1e8b0b5c2..340dc63406 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -6474,6 +6474,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_indkey,
i_indisclustered,
i_indisreplident,
+ i_indnullsnotdistinct,
i_contype,
i_conname,
i_condeferrable,
@@ -6550,14 +6551,21 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
"(SELECT pg_catalog.array_agg(attstattarget ORDER BY attnum) "
" FROM pg_catalog.pg_attribute "
" WHERE attrelid = i.indexrelid AND "
- " attstattarget >= 0) AS indstatvals ");
+ " attstattarget >= 0) AS indstatvals, ");
else
appendPQExpBuffer(query,
"0 AS parentidx, "
"i.indnatts AS indnkeyatts, "
"i.indnatts AS indnatts, "
"'' AS indstatcols, "
- "'' AS indstatvals ");
+ "'' AS indstatvals, ");
+
+ if (fout->remoteVersion >= 150000)
+ appendPQExpBuffer(query,
+ "i.indnullsnotdistinct ");
+ else
+ appendPQExpBuffer(query,
+ "false AS indnullsnotdistinct ");
/*
* The point of the messy-looking outer join is to find a constraint that
@@ -6620,6 +6628,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
i_indkey = PQfnumber(res, "indkey");
i_indisclustered = PQfnumber(res, "indisclustered");
i_indisreplident = PQfnumber(res, "indisreplident");
+ i_indnullsnotdistinct = PQfnumber(res, "indnullsnotdistinct");
i_contype = PQfnumber(res, "contype");
i_conname = PQfnumber(res, "conname");
i_condeferrable = PQfnumber(res, "condeferrable");
@@ -6696,6 +6705,7 @@ getIndexes(Archive *fout, TableInfo tblinfo[], int numTables)
indxinfo[j].indkeys, indxinfo[j].indnattrs);
indxinfo[j].indisclustered = (PQgetvalue(res, j, i_indisclustered)[0] == 't');
indxinfo[j].indisreplident = (PQgetvalue(res, j, i_indisreplident)[0] == 't');
+ indxinfo[j].indnullsnotdistinct = (PQgetvalue(res, j, i_indnullsnotdistinct)[0] == 't');
indxinfo[j].parentidx = atooid(PQgetvalue(res, j, i_parentidx));
indxinfo[j].partattaches = (SimplePtrList)
{
@@ -16112,8 +16122,11 @@ dumpConstraint(Archive *fout, const ConstraintInfo *coninfo)
}
else
{
- appendPQExpBuffer(q, "%s (",
+ appendPQExpBuffer(q, "%s",
coninfo->contype == 'p' ? "PRIMARY KEY" : "UNIQUE");
+ if (indxinfo->indnullsnotdistinct)
+ appendPQExpBuffer(q, " NULLS NOT DISTINCT");
+ appendPQExpBuffer(q, " (");
for (k = 0; k < indxinfo->indnkeyattrs; k++)
{
int indkey = (int) indxinfo->indkeys[k];
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 066a129ee5..9965ac2518 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -398,6 +398,7 @@ typedef struct _indxInfo
* contains both key and nonkey attributes */
bool indisclustered;
bool indisreplident;
+ bool indnullsnotdistinct;
Oid parentidx; /* if a partition, parent index OID */
SimplePtrList partattaches; /* if partitioned, partition attach objects */
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 40433e32fa..c5b07ab8a1 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2181,6 +2181,11 @@ describeOneTableDetails(const char *schemaname,
else
appendPQExpBufferStr(&buf, "false AS indisreplident,\n");
+ if (pset.sversion >= 150000)
+ appendPQExpBufferStr(&buf, "i.indnullsnotdistinct,\n");
+ else
+ appendPQExpBufferStr(&buf, "false AS indnullsnotdistinct,\n");
+
appendPQExpBuffer(&buf, " a.amname, c2.relname, "
"pg_catalog.pg_get_expr(i.indpred, i.indrelid, true)\n"
"FROM pg_catalog.pg_index i, pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_am a\n"
@@ -2205,14 +2210,20 @@ describeOneTableDetails(const char *schemaname,
char *deferrable = PQgetvalue(result, 0, 4);
char *deferred = PQgetvalue(result, 0, 5);
char *indisreplident = PQgetvalue(result, 0, 6);
- char *indamname = PQgetvalue(result, 0, 7);
- char *indtable = PQgetvalue(result, 0, 8);
- char *indpred = PQgetvalue(result, 0, 9);
+ char *indnullsnotdistinct = PQgetvalue(result, 0, 7);
+ char *indamname = PQgetvalue(result, 0, 8);
+ char *indtable = PQgetvalue(result, 0, 9);
+ char *indpred = PQgetvalue(result, 0, 10);
if (strcmp(indisprimary, "t") == 0)
printfPQExpBuffer(&tmpbuf, _("primary key, "));
else if (strcmp(indisunique, "t") == 0)
- printfPQExpBuffer(&tmpbuf, _("unique, "));
+ {
+ printfPQExpBuffer(&tmpbuf, _("unique"));
+ if (strcmp(indnullsnotdistinct, "t") == 0)
+ appendPQExpBufferStr(&tmpbuf, _(" nulls not distinct"));
+ appendPQExpBuffer(&tmpbuf, _(", "));
+ }
else
resetPQExpBuffer(&tmpbuf);
appendPQExpBuffer(&tmpbuf, "%s, ", indamname);
diff --git a/src/include/catalog/pg_index.h b/src/include/catalog/pg_index.h
index c31111495f..f853846ee1 100644
--- a/src/include/catalog/pg_index.h
+++ b/src/include/catalog/pg_index.h
@@ -34,6 +34,7 @@ CATALOG(pg_index,2610,IndexRelationId) BKI_SCHEMA_MACRO
int16 indnatts; /* total number of columns in index */
int16 indnkeyatts; /* number of key columns in index */
bool indisunique; /* is this a unique index? */
+ bool indnullsnotdistinct; /* null treatment in unique index */
bool indisprimary; /* is this index for primary key? */
bool indisexclusion; /* is this index for exclusion constraint? */
bool indimmediate; /* is uniqueness enforced immediately? */
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 4ea8735dd8..dd95dc40c7 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -173,6 +173,7 @@ typedef struct IndexInfo
uint16 *ii_UniqueStrats; /* array with one entry per column */
Datum *ii_OpclassOptions; /* array with one entry per column */
bool ii_Unique;
+ bool ii_NullsNotDistinct;
bool ii_ReadyForInserts;
bool ii_CheckedUnchanged;
bool ii_IndexUnchanged;
diff --git a/src/include/nodes/makefuncs.h b/src/include/nodes/makefuncs.h
index fe173101d1..50de4c62af 100644
--- a/src/include/nodes/makefuncs.h
+++ b/src/include/nodes/makefuncs.h
@@ -96,7 +96,7 @@ extern List *make_ands_implicit(Expr *clause);
extern IndexInfo *makeIndexInfo(int numattrs, int numkeyattrs, Oid amoid,
List *expressions, List *predicates,
- bool unique, bool isready, bool concurrent);
+ bool unique, bool nulls_not_distinct, bool isready, bool concurrent);
extern DefElem *makeDefElem(char *name, Node *arg, int location);
extern DefElem *makeDefElemExtended(char *nameSpace, char *name, Node *arg,
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 3e9bdc781f..37fcc4c9b5 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2277,6 +2277,7 @@ typedef struct Constraint
char generated_when; /* ALWAYS or BY DEFAULT */
/* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */
+ bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
List *keys; /* String nodes naming referenced key
* column(s) */
List *including; /* String nodes naming referenced nonkey
@@ -2905,6 +2906,7 @@ typedef struct IndexStmt
SubTransactionId oldFirstRelfilenodeSubid; /* rd_firstRelfilenodeSubid of
* oldNode */
bool unique; /* is index unique? */
+ bool nulls_not_distinct; /* null treatment for UNIQUE constraints */
bool primary; /* is index a primary key? */
bool isconstraint; /* is it for a pkey/unique constraint? */
bool deferrable; /* is the constraint DEFERRABLE? */
diff --git a/src/include/utils/tuplesort.h b/src/include/utils/tuplesort.h
index 37f79de8c6..da5ba59198 100644
--- a/src/include/utils/tuplesort.h
+++ b/src/include/utils/tuplesort.h
@@ -208,6 +208,7 @@ extern Tuplesortstate *tuplesort_begin_cluster(TupleDesc tupDesc,
extern Tuplesortstate *tuplesort_begin_index_btree(Relation heapRel,
Relation indexRel,
bool enforceUnique,
+ bool uniqueNullsNotDistinct,
int workMem, SortCoordinate coordinate,
bool randomAccess);
extern Tuplesortstate *tuplesort_begin_index_hash(Relation heapRel,
diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out
index e32cf8bb57..36ccbb5f15 100644
--- a/src/test/regress/expected/constraints.out
+++ b/src/test/regress/expected/constraints.out
@@ -445,6 +445,29 @@ SELECT * FROM UNIQUE_TBL;
6 | six-upsert-insert
(7 rows)
+DROP TABLE UNIQUE_TBL;
+CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+INSERT INTO UNIQUE_TBL VALUES (2, 'two');
+INSERT INTO UNIQUE_TBL VALUES (1, 'three');
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(1) already exists.
+INSERT INTO UNIQUE_TBL VALUES (4, 'four');
+INSERT INTO UNIQUE_TBL VALUES (5, 'one');
+INSERT INTO UNIQUE_TBL (t) VALUES ('six');
+INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
+ERROR: duplicate key value violates unique constraint "unique_tbl_i_key"
+DETAIL: Key (i)=(null) already exists.
+SELECT * FROM UNIQUE_TBL;
+ i | t
+---+------
+ 1 | one
+ 2 | two
+ 4 | four
+ 5 | one
+ | six
+(5 rows)
+
DROP TABLE UNIQUE_TBL;
CREATE TABLE UNIQUE_TBL (i int, t text,
UNIQUE(i,t));
diff --git a/src/test/regress/expected/create_index.out b/src/test/regress/expected/create_index.out
index daf75dd5c4..53c8e830ce 100644
--- a/src/test/regress/expected/create_index.out
+++ b/src/test/regress/expected/create_index.out
@@ -1272,6 +1272,67 @@ SELECT count(*) FROM tenk1 WHERE stringu1 = 'TVAAAA';
DROP INDEX hash_tuplesort_idx;
RESET maintenance_work_mem;
--
+-- Test unique null behavior
+--
+CREATE TABLE unique_tbl (i int, t text);
+CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
+CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;
+INSERT INTO unique_tbl VALUES (1, 'one');
+INSERT INTO unique_tbl VALUES (2, 'two');
+INSERT INTO unique_tbl VALUES (3, 'three');
+INSERT INTO unique_tbl VALUES (4, 'four');
+INSERT INTO unique_tbl VALUES (5, 'one');
+INSERT INTO unique_tbl (t) VALUES ('six');
+INSERT INTO unique_tbl (t) VALUES ('seven'); -- error from unique_idx2
+ERROR: duplicate key value violates unique constraint "unique_idx2"
+DETAIL: Key (i)=(null) already exists.
+DROP INDEX unique_idx1, unique_idx2;
+INSERT INTO unique_tbl (t) VALUES ('seven');
+-- build indexes on filled table
+CREATE UNIQUE INDEX unique_idx3 ON unique_tbl (i) NULLS DISTINCT; -- ok
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- error
+ERROR: could not create unique index "unique_idx4"
+DETAIL: Key (i)=(null) is duplicated.
+DELETE FROM unique_tbl WHERE t = 'seven';
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- ok now
+\d unique_tbl
+ Table "public.unique_tbl"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ i | integer | | |
+ t | text | | |
+Indexes:
+ "unique_idx3" UNIQUE, btree (i)
+ "unique_idx4" UNIQUE, btree (i) NULLS NOT DISTINCT
+
+\d unique_idx3
+ Index "public.unique_idx3"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ i | integer | yes | i
+unique, btree, for table "public.unique_tbl"
+
+\d unique_idx4
+ Index "public.unique_idx4"
+ Column | Type | Key? | Definition
+--------+---------+------+------------
+ i | integer | yes | i
+unique nulls not distinct, btree, for table "public.unique_tbl"
+
+SELECT pg_get_indexdef('unique_idx3'::regclass);
+ pg_get_indexdef
+----------------------------------------------------------------------
+ CREATE UNIQUE INDEX unique_idx3 ON public.unique_tbl USING btree (i)
+(1 row)
+
+SELECT pg_get_indexdef('unique_idx4'::regclass);
+ pg_get_indexdef
+-----------------------------------------------------------------------------------------
+ CREATE UNIQUE INDEX unique_idx4 ON public.unique_tbl USING btree (i) NULLS NOT DISTINCT
+(1 row)
+
+DROP TABLE unique_tbl;
+--
-- Test functional index
--
CREATE TABLE func_index_heap (f1 text, f2 text);
diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql
index 458f805778..34de0c969a 100644
--- a/src/test/regress/sql/constraints.sql
+++ b/src/test/regress/sql/constraints.sql
@@ -306,6 +306,20 @@ CREATE TABLE UNIQUE_TBL (i int UNIQUE, t text);
DROP TABLE UNIQUE_TBL;
+CREATE TABLE UNIQUE_TBL (i int UNIQUE NULLS NOT DISTINCT, t text);
+
+INSERT INTO UNIQUE_TBL VALUES (1, 'one');
+INSERT INTO UNIQUE_TBL VALUES (2, 'two');
+INSERT INTO UNIQUE_TBL VALUES (1, 'three');
+INSERT INTO UNIQUE_TBL VALUES (4, 'four');
+INSERT INTO UNIQUE_TBL VALUES (5, 'one');
+INSERT INTO UNIQUE_TBL (t) VALUES ('six');
+INSERT INTO UNIQUE_TBL (t) VALUES ('seven');
+
+SELECT * FROM UNIQUE_TBL;
+
+DROP TABLE UNIQUE_TBL;
+
CREATE TABLE UNIQUE_TBL (i int, t text,
UNIQUE(i,t));
diff --git a/src/test/regress/sql/create_index.sql b/src/test/regress/sql/create_index.sql
index 8b353be16e..9003950a1f 100644
--- a/src/test/regress/sql/create_index.sql
+++ b/src/test/regress/sql/create_index.sql
@@ -387,6 +387,43 @@ CREATE INDEX hash_tuplesort_idx ON tenk1 USING hash (stringu1 name_ops) WITH (fi
RESET maintenance_work_mem;
+--
+-- Test unique null behavior
+--
+CREATE TABLE unique_tbl (i int, t text);
+
+CREATE UNIQUE INDEX unique_idx1 ON unique_tbl (i) NULLS DISTINCT;
+CREATE UNIQUE INDEX unique_idx2 ON unique_tbl (i) NULLS NOT DISTINCT;
+
+INSERT INTO unique_tbl VALUES (1, 'one');
+INSERT INTO unique_tbl VALUES (2, 'two');
+INSERT INTO unique_tbl VALUES (3, 'three');
+INSERT INTO unique_tbl VALUES (4, 'four');
+INSERT INTO unique_tbl VALUES (5, 'one');
+INSERT INTO unique_tbl (t) VALUES ('six');
+INSERT INTO unique_tbl (t) VALUES ('seven'); -- error from unique_idx2
+
+DROP INDEX unique_idx1, unique_idx2;
+
+INSERT INTO unique_tbl (t) VALUES ('seven');
+
+-- build indexes on filled table
+CREATE UNIQUE INDEX unique_idx3 ON unique_tbl (i) NULLS DISTINCT; -- ok
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- error
+
+DELETE FROM unique_tbl WHERE t = 'seven';
+
+CREATE UNIQUE INDEX unique_idx4 ON unique_tbl (i) NULLS NOT DISTINCT; -- ok now
+
+\d unique_tbl
+\d unique_idx3
+\d unique_idx4
+SELECT pg_get_indexdef('unique_idx3'::regclass);
+SELECT pg_get_indexdef('unique_idx4'::regclass);
+
+DROP TABLE unique_tbl;
+
+
--
-- Test functional index
--
base-commit: f032f63e727c1ab07603b3d1cd88d50f850d5738
--
2.34.1
Re: UNIQUE null treatment option
Makes sense. Here is an updated patch with this change.
I didn't end up renaming anynullkeys. I came up with names like
"anyalwaysdistinctkeys", but in the end that felt too abstract, and
moreover, it would require rewriting a bunch of code comments that refer
to null values in this context. Since as you wrote, anynullkeys is just
a local concern between two functions, this slight inaccuracy is perhaps
better than some highly general but unclear terminology.
Agree with that. With the comment it is clear how it works.
I've looked at the patch v3. It seems good enough for me. CFbot tests have
also come green.
Suggest it is RFC now.
--
Best regards,
Pavel Borisov
Postgres Professional: http://postgrespro.com <http://www.postgrespro.com>
Re: UNIQUE null treatment option
On 28.01.22 13:56, Pavel Borisov wrote:
Makes sense. Here is an updated patch with this change.
I didn't end up renaming anynullkeys. I came up with names like
"anyalwaysdistinctkeys", but in the end that felt too abstract, and
moreover, it would require rewriting a bunch of code comments that
refer
to null values in this context. Since as you wrote, anynullkeys is
just
a local concern between two functions, this slight inaccuracy is
perhaps
better than some highly general but unclear terminology.Agree with that. With the comment it is clear how it works.
I've looked at the patch v3. It seems good enough for me. CFbot tests
have also come green.
Suggest it is RFC now.
Committed. Thanks.