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)