diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/executor/execIndexing.c | 18 | ||||
| -rw-r--r-- | src/test/regress/expected/constraints.out | 5 | ||||
| -rw-r--r-- | src/test/regress/sql/constraints.sql | 5 |
3 files changed, 18 insertions, 10 deletions
diff --git a/src/backend/executor/execIndexing.c b/src/backend/executor/execIndexing.c index 0cb0b8f1111..6a8735edf7f 100644 --- a/src/backend/executor/execIndexing.c +++ b/src/backend/executor/execIndexing.c @@ -699,13 +699,19 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index, } /* - * If any of the input values are NULL, the constraint check is assumed to - * pass (i.e., we assume the operators are strict). + * If any of the input values are NULL, and the index uses the default + * nulls-are-distinct mode, the constraint check is assumed to pass (i.e., + * we assume the operators are strict). Otherwise, we interpret the + * constraint as specifying IS NULL for each column whose input value is + * NULL. */ - for (i = 0; i < indnkeyatts; i++) + if (!indexInfo->ii_NullsNotDistinct) { - if (isnull[i]) - return true; + for (i = 0; i < indnkeyatts; i++) + { + if (isnull[i]) + return true; + } } /* @@ -717,7 +723,7 @@ check_exclusion_or_unique_constraint(Relation heap, Relation index, for (i = 0; i < indnkeyatts; i++) { ScanKeyEntryInitialize(&scankeys[i], - 0, + isnull[i] ? SK_ISNULL | SK_SEARCHNULL : 0, i + 1, constr_strats[i], InvalidOid, diff --git a/src/test/regress/expected/constraints.out b/src/test/regress/expected/constraints.out index 36ccbb5f155..05b7244e4a7 100644 --- a/src/test/regress/expected/constraints.out +++ b/src/test/regress/expected/constraints.out @@ -449,15 +449,16 @@ 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 (1, 'three'); -- fail 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'); +INSERT INTO UNIQUE_TBL (t) VALUES ('seven'); -- fail ERROR: duplicate key value violates unique constraint "unique_tbl_i_key" DETAIL: Key (i)=(null) already exists. +INSERT INTO UNIQUE_TBL (t) VALUES ('eight') ON CONFLICT DO NOTHING; -- no-op SELECT * FROM UNIQUE_TBL; i | t ---+------ diff --git a/src/test/regress/sql/constraints.sql b/src/test/regress/sql/constraints.sql index 34de0c969ae..833819a32e2 100644 --- a/src/test/regress/sql/constraints.sql +++ b/src/test/regress/sql/constraints.sql @@ -310,11 +310,12 @@ 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 (1, 'three'); -- fail 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'); +INSERT INTO UNIQUE_TBL (t) VALUES ('seven'); -- fail +INSERT INTO UNIQUE_TBL (t) VALUES ('eight') ON CONFLICT DO NOTHING; -- no-op SELECT * FROM UNIQUE_TBL; |