Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

CREATE TABLE

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

CREATE INDEX

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

INSERT 0 1

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

INSERT 0 1

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.

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

INSERT 0 1