Skip to content

Commit 94aa7cc

Browse files
peterepetere
committed
Add UNIQUE null treatment option
The SQL standard has been ambiguous about whether null values in unique constraints should be considered equal or not. Different implementations have different behaviors. In the SQL:202x draft, this has been formalized by making this implementation-defined and adding an option on unique constraint definitions UNIQUE [ NULLS [NOT] DISTINCT ] to choose a behavior explicitly. This patch adds this option to PostgreSQL. The default behavior remains UNIQUE NULLS DISTINCT. Making this happen in the btree code is pretty easy; most of the patch is just to carry the flag around to all the places that need it. The CREATE UNIQUE INDEX syntax extension is not from the standard, it's my own invention. I named all the internal flags, catalog columns, etc. in the negative ("nulls not distinct") so that the default PostgreSQL behavior is the default if the flag is false. Reviewed-by: Maxim Orlov <orlovmg@gmail.com> Reviewed-by: Pavel Borisov <pashkin.elfe@gmail.com> Discussion: https://www.postgresql.org/message-id/flat/84e5ee1b-387e-9a54-c326-9082674bde78@enterprisedb.com
1 parent f862d57 commit 94aa7cc

36 files changed

+348
-57
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -4265,6 +4265,19 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
42654265
</para></entry>
42664266
</row>
42674267

4268+
<row>
4269+
<entry role="catalog_table_entry"><para role="column_definition">
4270+
<structfield>indnullsnotdistinct</structfield> <type>bool</type>
4271+
</para>
4272+
<para>
4273+
This value is only used for unique indexes. If false, this unique
4274+
index will consider null values distinct (so the index can contain
4275+
multiple null values in a column, the default PostgreSQL behavior). If
4276+
it is true, it will consider null values to be equal (so the index can
4277+
only contain one null value in a column).
4278+
</para></entry>
4279+
</row>
4280+
42684281
<row>
42694282
<entry role="catalog_table_entry"><para role="column_definition">
42704283
<structfield>indisprimary</structfield> <type>bool</type>

‎doc/src/sgml/ddl.sgml

Lines changed: 24 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -759,14 +759,33 @@ CREATE TABLE products (
759759
In general, a unique constraint is violated if there is more than
760760
one row in the table where the values of all of the
761761
columns included in the constraint are equal.
762-
However, two null values are never considered equal in this
762+
By default, two null values are not considered equal in this
763763
comparison. That means even in the presence of a
764764
unique constraint it is possible to store duplicate
765765
rows that contain a null value in at least one of the constrained
766-
columns. This behavior conforms to the SQL standard, but we have
767-
heard that other SQL databases might not follow this rule. So be
768-
careful when developing applications that are intended to be
769-
portable.
766+
columns. This behavior can be changed by adding the clause <literal>NULLS
767+
NOT DISTINCT</literal>, like
768+
<programlisting>
769+
CREATE TABLE products (
770+
product_no integer UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis>,
771+
name text,
772+
price numeric
773+
);
774+
</programlisting>
775+
or
776+
<programlisting>
777+
CREATE TABLE products (
778+
product_no integer,
779+
name text,
780+
price numeric,
781+
UNIQUE <emphasis>NULLS NOT DISTINCT</emphasis> (product_no)
782+
);
783+
</programlisting>
784+
The default behavior can be specified explicitly using <literal>NULLS
785+
DISTINCT</literal>. The default null treatment in unique constraints is
786+
implementation-defined according to the SQL standard, and other
787+
implementations have a different behavior. So be careful when developing
788+
applications that are intended to be portable.
770789
</para>
771790
</sect2>
772791

‎doc/src/sgml/information_schema.sgml

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6899,6 +6899,18 @@ ORDER BY c.ordinal_position;
68996899
<literal>YES</literal>)
69006900
</para></entry>
69016901
</row>
6902+
6903+
<row>
6904+
<entry role="catalog_table_entry"><para role="column_definition">
6905+
<structfield>nulls_distinct</structfield> <type>yes_or_no</type>
6906+
</para>
6907+
<para>
6908+
If the constraint is a unique constraint, then <literal>YES</literal>
6909+
if the constraint treats nulls as distinct or <literal>NO</literal> if
6910+
it treats nulls as not distinct, otherwise null for other types of
6911+
constraints.
6912+
</para></entry>
6913+
</row>
69026914
</tbody>
69036915
</tgroup>
69046916
</table>

‎doc/src/sgml/ref/alter_table.sgml

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -103,7 +103,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
103103
DEFAULT <replaceable>default_expr</replaceable> |
104104
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
105105
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
106-
UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
106+
UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
107107
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
108108
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
109109
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
@@ -113,7 +113,7 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
113113

114114
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
115115
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
116-
UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
116+
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
117117
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
118118
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> ) ] |
119119
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]

‎doc/src/sgml/ref/create_index.sgml

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -24,6 +24,7 @@ PostgreSQL documentation
2424
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> ]
2525
( { <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 } ] [, ...] )
2626
[ INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...] ) ]
27+
[ NULLS [ NOT ] DISTINCT ]
2728
[ WITH ( <replaceable class="parameter">storage_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
2829
[ TABLESPACE <replaceable class="parameter">tablespace_name</replaceable> ]
2930
[ WHERE <replaceable class="parameter">predicate</replaceable> ]
@@ -334,6 +335,18 @@ CREATE [ UNIQUE ] INDEX [ CONCURRENTLY ] [ [ IF NOT EXISTS ] <replaceable class=
334335
</listitem>
335336
</varlistentry>
336337

338+
<varlistentry>
339+
<term><literal>NULLS DISTINCT</literal></term>
340+
<term><literal>NULLS NOT DISTINCT</literal></term>
341+
<listitem>
342+
<para>
343+
Specifies whether for a unique index, null values should be considered
344+
distinct (not equal). The default is that they are distinct, so that
345+
a unique index could contain multiple null values in a column.
346+
</para>
347+
</listitem>
348+
</varlistentry>
349+
337350
<varlistentry>
338351
<term><replaceable class="parameter">storage_parameter</replaceable></term>
339352
<listitem>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 6 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -67,7 +67,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
6767
DEFAULT <replaceable>default_expr</replaceable> |
6868
GENERATED ALWAYS AS ( <replaceable>generation_expr</replaceable> ) STORED |
6969
GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
70-
UNIQUE <replaceable class="parameter">index_parameters</replaceable> |
70+
UNIQUE [ NULLS [ NOT ] DISTINCT ] <replaceable class="parameter">index_parameters</replaceable> |
7171
PRIMARY KEY <replaceable class="parameter">index_parameters</replaceable> |
7272
REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
7373
[ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] }
@@ -77,7 +77,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI
7777

7878
[ CONSTRAINT <replaceable class="parameter">constraint_name</replaceable> ]
7979
{ CHECK ( <replaceable class="parameter">expression</replaceable> ) [ NO INHERIT ] |
80-
UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
80+
UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
8181
PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> |
8282
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> ) ] |
8383
FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ]
@@ -917,8 +917,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
917917
</varlistentry>
918918

919919
<varlistentry>
920-
<term><literal>UNIQUE</literal> (column constraint)</term>
921-
<term><literal>UNIQUE ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
920+
<term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ]</literal> (column constraint)</term>
921+
<term><literal>UNIQUE [ NULLS [ NOT ] DISTINCT ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] )</literal>
922922
<optional> <literal>INCLUDE ( <replaceable class="parameter">column_name</replaceable> [, ...])</literal> </optional> (table constraint)</term>
923923

924924
<listitem>
@@ -934,7 +934,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
934934

935935
<para>
936936
For the purpose of a unique constraint, null values are not
937-
considered equal.
937+
considered equal, unless <literal>NULLS NOT DISTINCT</literal> is
938+
specified.
938939
</para>
939940

940941
<para>

‎src/backend/access/nbtree/nbtinsert.c

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -398,9 +398,9 @@ _bt_search_insert(Relation rel, BTInsertState insertstate)
398398
* _bt_findinsertloc() to reuse most of the binary search work we do
399399
* here.
400400
*
401-
* Do not call here when there are NULL values in scan key. NULL should be
402-
* considered unequal to NULL when checking for duplicates, but we are not
403-
* prepared to handle that correctly.
401+
* This code treats NULLs as equal, unlike the default semantics for unique
402+
* indexes. So do not call here when there are NULL values in scan key and
403+
* the index uses the default NULLS DISTINCT mode.
404404
*/
405405
static TransactionId
406406
_bt_check_unique(Relation rel, BTInsertState insertstate, Relation heapRel,

‎src/backend/access/nbtree/nbtsort.c

Lines changed: 13 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -89,6 +89,7 @@ typedef struct BTSpool
8989
Relation heap;
9090
Relation index;
9191
bool isunique;
92+
bool nulls_not_distinct;
9293
} BTSpool;
9394

9495
/*
@@ -106,6 +107,7 @@ typedef struct BTShared
106107
Oid heaprelid;
107108
Oid indexrelid;
108109
bool isunique;
110+
bool nulls_not_distinct;
109111
bool isconcurrent;
110112
int scantuplesortstates;
111113

@@ -206,6 +208,7 @@ typedef struct BTLeader
206208
typedef struct BTBuildState
207209
{
208210
bool isunique;
211+
bool nulls_not_distinct;
209212
bool havedead;
210213
Relation heap;
211214
BTSpool *spool;
@@ -307,6 +310,7 @@ btbuild(Relation heap, Relation index, IndexInfo *indexInfo)
307310
#endif /* BTREE_BUILD_STATS */
308311

309312
buildstate.isunique = indexInfo->ii_Unique;
313+
buildstate.nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
310314
buildstate.havedead = false;
311315
buildstate.heap = heap;
312316
buildstate.spool = NULL;
@@ -380,6 +384,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
380384
btspool->heap = heap;
381385
btspool->index = index;
382386
btspool->isunique = indexInfo->ii_Unique;
387+
btspool->nulls_not_distinct = indexInfo->ii_NullsNotDistinct;
383388

384389
/* Save as primary spool */
385390
buildstate->spool = btspool;
@@ -429,6 +434,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
429434
*/
430435
buildstate->spool->sortstate =
431436
tuplesort_begin_index_btree(heap, index, buildstate->isunique,
437+
buildstate->nulls_not_distinct,
432438
maintenance_work_mem, coordinate,
433439
false);
434440

@@ -468,7 +474,7 @@ _bt_spools_heapscan(Relation heap, Relation index, BTBuildState *buildstate,
468474
* full, so we give it only work_mem
469475
*/
470476
buildstate->spool2->sortstate =
471-
tuplesort_begin_index_btree(heap, index, false, work_mem,
477+
tuplesort_begin_index_btree(heap, index, false, false, work_mem,
472478
coordinate2, false);
473479
}
474480

@@ -1554,6 +1560,7 @@ _bt_begin_parallel(BTBuildState *buildstate, bool isconcurrent, int request)
15541560
btshared->heaprelid = RelationGetRelid(btspool->heap);
15551561
btshared->indexrelid = RelationGetRelid(btspool->index);
15561562
btshared->isunique = btspool->isunique;
1563+
btshared->nulls_not_distinct = btspool->nulls_not_distinct;
15571564
btshared->isconcurrent = isconcurrent;
15581565
btshared->scantuplesortstates = scantuplesortstates;
15591566
ConditionVariableInit(&btshared->workersdonecv);
@@ -1747,6 +1754,7 @@ _bt_leader_participate_as_worker(BTBuildState *buildstate)
17471754
leaderworker->heap = buildstate->spool->heap;
17481755
leaderworker->index = buildstate->spool->index;
17491756
leaderworker->isunique = buildstate->spool->isunique;
1757+
leaderworker->nulls_not_distinct = buildstate->spool->nulls_not_distinct;
17501758

17511759
/* Initialize second spool, if required */
17521760
if (!btleader->btshared->isunique)
@@ -1846,6 +1854,7 @@ _bt_parallel_build_main(dsm_segment *seg, shm_toc *toc)
18461854
btspool->heap = heapRel;
18471855
btspool->index = indexRel;
18481856
btspool->isunique = btshared->isunique;
1857+
btspool->nulls_not_distinct = btshared->nulls_not_distinct;
18491858

18501859
/* Look up shared state private to tuplesort.c */
18511860
sharedsort = shm_toc_lookup(toc, PARALLEL_KEY_TUPLESORT, false);
@@ -1928,6 +1937,7 @@ _bt_parallel_scan_and_sort(BTSpool *btspool, BTSpool *btspool2,
19281937
btspool->sortstate = tuplesort_begin_index_btree(btspool->heap,
19291938
btspool->index,
19301939
btspool->isunique,
1940+
btspool->nulls_not_distinct,
19311941
sortmem, coordinate,
19321942
false);
19331943

@@ -1950,13 +1960,14 @@ _bt_parallel_scan_and_sort(BTSpool *btspool, BTSpool *btspool2,
19501960
coordinate2->nParticipants = -1;
19511961
coordinate2->sharedsort = sharedsort2;
19521962
btspool2->sortstate =
1953-
tuplesort_begin_index_btree(btspool->heap, btspool->index, false,
1963+
tuplesort_begin_index_btree(btspool->heap, btspool->index, false, false,
19541964
Min(sortmem, work_mem), coordinate2,
19551965
false);
19561966
}
19571967

19581968
/* Fill in buildstate for _bt_build_callback() */
19591969
buildstate.isunique = btshared->isunique;
1970+
buildstate.nulls_not_distinct = btshared->nulls_not_distinct;
19601971
buildstate.havedead = false;
19611972
buildstate.heap = btspool->heap;
19621973
buildstate.spool = btspool;

‎src/backend/access/nbtree/nbtutils.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -165,6 +165,13 @@ _bt_mkscankey(Relation rel, IndexTuple itup)
165165
key->anynullkeys = true;
166166
}
167167

168+
/*
169+
* In NULLS NOT DISTINCT mode, we pretend that there are no null keys, so
170+
* that full uniqueness check is done.
171+
*/
172+
if (rel->rd_index->indnullsnotdistinct)
173+
key->anynullkeys = false;
174+
168175
return key;
169176
}
170177

‎src/backend/catalog/index.c

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -614,6 +614,7 @@ UpdateIndexRelation(Oid indexoid,
614614
values[Anum_pg_index_indnatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexAttrs);
615615
values[Anum_pg_index_indnkeyatts - 1] = Int16GetDatum(indexInfo->ii_NumIndexKeyAttrs);
616616
values[Anum_pg_index_indisunique - 1] = BoolGetDatum(indexInfo->ii_Unique);
617+
values[Anum_pg_index_indnullsnotdistinct - 1] = BoolGetDatum(indexInfo->ii_NullsNotDistinct);
617618
values[Anum_pg_index_indisprimary - 1] = BoolGetDatum(primary);
618619
values[Anum_pg_index_indisexclusion - 1] = BoolGetDatum(isexclusion);
619620
values[Anum_pg_index_indimmediate - 1] = BoolGetDatum(immediate);
@@ -1368,6 +1369,7 @@ index_concurrently_create_copy(Relation heapRelation, Oid oldIndexId,
13681369
indexExprs,
13691370
indexPreds,
13701371
oldInfo->ii_Unique,
1372+
oldInfo->ii_NullsNotDistinct,
13711373
false, /* not ready for inserts */
13721374
true);
13731375

@@ -2440,6 +2442,7 @@ BuildIndexInfo(Relation index)
24402442
RelationGetIndexExpressions(index),
24412443
RelationGetIndexPredicate(index),
24422444
indexStruct->indisunique,
2445+
indexStruct->indnullsnotdistinct,
24432446
indexStruct->indisready,
24442447
false);
24452448

@@ -2499,6 +2502,7 @@ BuildDummyIndexInfo(Relation index)
24992502
RelationGetDummyIndexExpressions(index),
25002503
NIL,
25012504
indexStruct->indisunique,
2505+
indexStruct->indnullsnotdistinct,
25022506
indexStruct->indisready,
25032507
false);
25042508

@@ -2532,6 +2536,9 @@ CompareIndexInfo(IndexInfo *info1, IndexInfo *info2,
25322536
if (info1->ii_Unique != info2->ii_Unique)
25332537
return false;
25342538

2539+
if (info1->ii_NullsNotDistinct != info2->ii_NullsNotDistinct)
2540+
return false;
2541+
25352542
/* indexes are only equivalent if they have the same access method */
25362543
if (info1->ii_Am != info2->ii_Am)
25372544
return false;

‎src/backend/catalog/information_schema.sql

Lines changed: 7 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -1838,7 +1838,11 @@ CREATE VIEW table_constraints AS
18381838
AS is_deferrable,
18391839
CAST(CASE WHEN c.condeferred THEN 'YES' ELSE 'NO' END AS yes_or_no)
18401840
AS initially_deferred,
1841-
CAST('YES' AS yes_or_no) AS enforced
1841+
CAST('YES' AS yes_or_no) AS enforced,
1842+
CAST(CASE WHEN c.contype = 'u'
1843+
THEN CASE WHEN (SELECT NOT indnullsnotdistinct FROM pg_index WHERE indexrelid = conindid) THEN 'YES' ELSE 'NO' END
1844+
END
1845+
AS yes_or_no) AS nulls_distinct
18421846

18431847
FROM pg_namespace nc,
18441848
pg_namespace nr,
@@ -1868,7 +1872,8 @@ CREATE VIEW table_constraints AS
18681872
CAST('CHECK' AS character_data) AS constraint_type,
18691873
CAST('NO' AS yes_or_no) AS is_deferrable,
18701874
CAST('NO' AS yes_or_no) AS initially_deferred,
1871-
CAST('YES' AS yes_or_no) AS enforced
1875+
CAST('YES' AS yes_or_no) AS enforced,
1876+
CAST(NULL AS yes_or_no) AS nulls_distinct
18721877

18731878
FROM pg_namespace nr,
18741879
pg_class r,

‎src/backend/catalog/sql_features.txt

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -228,6 +228,7 @@ F263 Comma-separated predicates in simple CASE expression NO
228228
F271 Compound character literals YES
229229
F281 LIKE enhancements YES
230230
F291 UNIQUE predicate NO
231+
F292 UNIQUE null treatment YES SQL:202x draft
231232
F301 CORRESPONDING in query expressions NO
232233
F302 INTERSECT table operator YES
233234
F302 INTERSECT table operator 01 INTERSECT DISTINCT table operator YES

‎src/backend/catalog/toasting.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -301,6 +301,7 @@ create_toast_table(Relation rel, Oid toastOid, Oid toastIndexOid,
301301
indexInfo->ii_ExclusionStrats = NULL;
302302
indexInfo->ii_OpclassOptions = NULL;
303303
indexInfo->ii_Unique = true;
304+
indexInfo->ii_NullsNotDistinct = false;
304305
indexInfo->ii_ReadyForInserts = true;
305306
indexInfo->ii_CheckedUnchanged = false;
306307
indexInfo->ii_IndexUnchanged = false;

‎src/backend/commands/indexcmds.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -226,7 +226,7 @@ CheckIndexCompatible(Oid oldId,
226226
* ii_NumIndexKeyAttrs with same value.
227227
*/
228228
indexInfo = makeIndexInfo(numberOfAttributes, numberOfAttributes,
229-
accessMethodId, NIL, NIL, false, false, false);
229+
accessMethodId, NIL, NIL, false, false, false, false);
230230
typeObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
231231
collationObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
232232
classObjectId = (Oid *) palloc(numberOfAttributes * sizeof(Oid));
@@ -867,6 +867,7 @@ DefineIndex(Oid relationId,
867867
NIL, /* expressions, NIL for now */
868868
make_ands_implicit((Expr *) stmt->whereClause),
869869
stmt->unique,
870+
stmt->nulls_not_distinct,
870871
!concurrent,
871872
concurrent);
872873

0 commit comments

Comments
 (0)