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?

GMB · Accepted Answer · 2020-01-30 21:02:29Z

4

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.

edited Jan 30, 2020 at 21:02

answered Jan 30, 2020 at 20:57

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nathan F. Over a year ago

This only seems to work in instances where the second column is a boolean type. What if i want the same effect, but where the second column is a string. And i want unique values in column1 for each distinct value in column2

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related