Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit94aa7cc

Browse files
committed
Add UNIQUE null treatment option
The SQL standard has been ambiguous about whether null values inunique constraints should be considered equal or not. Differentimplementations have different behaviors. In the SQL:202x draft, thishas been formalized by making this implementation-defined and addingan option on unique constraint definitions UNIQUE [ NULLS [NOT]DISTINCT ] to choose a behavior explicitly.This patch adds this option to PostgreSQL. The default behaviorremains UNIQUE NULLS DISTINCT. Making this happen in the btree codeis pretty easy; most of the patch is just to carry the flag around toall 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 thedefault 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 parentf862d57 commit94aa7cc

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 arenever considered equal in this
762+
By default, two null values arenot 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 NULLwhenchecking 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 herewhenthere are NULL values in scan key and
403+
*the index uses the default NULLS DISTINCT mode.
404404
*/
405405
staticTransactionId
406406
_bt_check_unique(Relationrel,BTInsertStateinsertstate,RelationheapRel,

‎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
Relationheap;
9090
Relationindex;
9191
boolisunique;
92+
boolnulls_not_distinct;
9293
}BTSpool;
9394

9495
/*
@@ -106,6 +107,7 @@ typedef struct BTShared
106107
Oidheaprelid;
107108
Oidindexrelid;
108109
boolisunique;
110+
boolnulls_not_distinct;
109111
boolisconcurrent;
110112
intscantuplesortstates;
111113

@@ -206,6 +208,7 @@ typedef struct BTLeader
206208
typedefstructBTBuildState
207209
{
208210
boolisunique;
211+
boolnulls_not_distinct;
209212
boolhavedead;
210213
Relationheap;
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
returnkey;
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 WHENc.condeferred THEN'YES' ELSE'NO' ENDAS 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 WHENc.contype='u'
1843+
THEN CASE WHEN (SELECT NOT indnullsnotdistinctFROM pg_indexWHERE 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(NULLAS 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 @@ F263Comma-separated predicates in simple CASE expressionNO
228228
F271Compound character literalsYES
229229
F281LIKE enhancementsYES
230230
F291UNIQUE predicateNO
231+
F292UNIQUE null treatmentYESSQL:202x draft
231232
F301CORRESPONDING in query expressionsNO
232233
F302INTERSECT table operatorYES
233234
F302INTERSECT table operator01INTERSECT DISTINCT table operatorYES

‎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)
[8]ページ先頭
©2009-2025 Movatter.jp