Here a usable example for an arbitrary number of columns (might require casting the columns to e.g. bit varying before
makingthe arrays):
Just replace c1,c2 with your (casted) number of columns on index creation.
create function fn(cs anyelement)
returns int
as
'select count(1) from unnest(cs) t(c) where c is not null;'
language sql
immutable;
create unique index idx on t ((
case
when fn(ARRAY[c1,c2])=0 then null
else ARRAY[c1,c2]
end
));
Result:
db=# insert into t(c1,c2) values (null,null);
INSERT 0 1
db=# insert into t(c1,c2) values (null,null);
INSERT 0 1
db=# insert into t(c1,c2) values (null,null);
INSERT 0 1
db=# insert into t(c1,c2) values (1,null);
INSERT 0 1
db=# insert into t(c1,c2) values (1,null);
ERROR: duplicate key value violates unique constraint "idx"
DETAIL: Key ((
CASE
WHEN fn(ARRAY[c1, c2]) = 0 THEN NULL::integer[]
ELSE ARRAY[c1, c2]
END))=({1,NULL}) already exists.
db=# insert into t(c1,c2) values (null,1);
INSERT 0 1
db=# insert into t(c1,c2) values (null,1);
ERROR: duplicate key value violates unique constraint "idx"
DETAIL: Key ((
CASE
WHEN fn(ARRAY[c1, c2]) = 0 THEN NULL::integer[]
ELSE ARRAY[c1, c2]
END))=({NULL,1}) already exists.
db=# insert into t(c1,c2) values (null,2);
INSERT 0 1
db=# insert into t(c1,c2) values (null,2);
ERROR: duplicate key value violates unique constraint "idx"
DETAIL: Key ((
CASE
WHEN fn(ARRAY[c1, c2]) = 0 THEN NULL::integer[]
ELSE ARRAY[c1, c2]
END))=({NULL,2}) already exists.
