Unique唯一性約束顧名思義要求值唯一，但由於NULL值什麼都不是，NULL跟NULL比較的值既不是真也不是假，因此通常情況下允許多個NULL值存在。
如果希望NULL也作為一個正常值來處理，在PG 15中可使用NULLS NOT DISTINCT標記

**測試指令碼**
```
drop table if exists t1;
create table t1(id int,c1 varchar,c2 varchar);
alter table t1 add unique(c1,c2);

insert into t1 select x,'c1',null from generate_series(1,10) as x;

select * from t1;

-- 
drop table if exists t2;
create table t2(id int,c1 varchar,c2 varchar);
alter table t2 add unique NULLS NOT DISTINCT (c1,c2);

insert into t2 select x,'c1',null from generate_series(1,10) as x;

select * from t2;

```

```
testdb=# drop table if exists t1;
DROP TABLE
testdb=# create table t1(id int,c1 varchar,c2 varchar);
CREATE TABLE
testdb=# alter table t1 add unique(c1,c2);
ALTER TABLE
testdb=#
testdb=# insert into t1 select x,'c1',null from generate_series(1,10) as x;
INSERT 0 10
testdb=# select * from t1;
 id | c1 | c2
----+----+----
  1 | c1 |
  2 | c1 |
  3 | c1 |
  4 | c1 |
  5 | c1 |
  6 | c1 |
  7 | c1 |
  8 | c1 |
  9 | c1 |
 10 | c1 |
(10 rows)

testdb=# --
testdb=# drop table if exists t2;
DROP TABLE
testdb=# create table t2(id int,c1 varchar,c2 varchar);
CREATE TABLE
testdb=# alter table t2 add unique NULLS NOT DISTINCT (c1,c2);
ALTER TABLE
testdb=#
testdb=# insert into t2 select x,'c1',null from generate_series(1,10) as x;
2022-11-15 14:47:13.539 CST [1674] ERROR:  duplicate key value violates unique constraint "t2_c1_c2_key"
2022-11-15 14:47:13.539 CST [1674] DETAIL:  Key (c1, c2)=(c1, null) already exists.
2022-11-15 14:47:13.539 CST [1674] STATEMENT:  insert into t2 select x,'c1',null from generate_series(1,10) as x;
ERROR:  duplicate key value violates unique constraint "t2_c1_c2_key"
DETAIL:  Key (c1, c2)=(c1, null) already exists.
testdb=#
testdb=# select * from t2;
 id | c1 | c2
----+----+----
(0 rows)

testdb=#

```

**參考資料**
[Postgres 15 improves UNIQUE and NULL](https://blog.rustprooflabs.com/2022/07/postgres-15-unique-improvement-with-null)

PostgreSQL DBA(194) - Unique&NULL

相關文章