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