Postgres - set unique constraint in a column based on another column

Question

I have a table like this:

CREATE TABLE schema.mytable
(
  id serial NOT NULL,
  number integer NOT NULL,
  state boolean NOT NULL,
);

I need to create an unique set of ´number´ but, the state column has to be true; if the state column is false, the numbers can be repeated, here is a example of what I need to be valid:

id  number      state
1   123         true
2   124         true
3   125         true
4   123         false
5   129         false

as you can see, number 123 is repeated but in one case the state is false and the other is true; this is incorrect:

id  number      state
1   123         true
2   124         true
3   125         true
4   123         true (*incorrect)
5   129         false

Also, it is possible that 123 is repeated two or more times with the false state; How can I achieve this?

Answer 1

You can't have a partial unique constraint, but you can create a partial unique index, which implements the very same functionality:

create unique index mytable_bk on mytable(number) where (state);

Demo on DB FIddle:

insert into mytable(id, number, state) values(1, 123, false);
-- 1 rows affected

insert into mytable(id, number, state) values(1, 123, true);
-- 1 rows affected

insert into mytable(id, number, state) values(1, 123, false);
-- 1 rows affected

insert into mytable(id, number, state) values(1, 123, true);
-- ERROR:  duplicate key value violates unique constraint "mytable_bk"
-- DETAIL:  Key (number)=(123) already exists.