Postgres DB constraint for a value of a column

Question

I have a UNIQUE constraint for columns username + user_status, such that we never allow two users that have the same name and status. However I wish this to apply only to the case where the status field is 'ACTIVE' but allow any number of records with the same username and status of 'INACTIVE'. Is this possible?

Which implies that you could never set a user to ACTIVE if a user with the same name is ACTIVE. — wildplasser
– wildplasser, Commented Sep 26, 2020 at 11:56

user330315 · Accepted Answer · 2020-09-26 10:19:32Z

5

You can create a filtered unique index:

create unique index on the_table (username)
where user_status = 'ACTIVE';

answered Sep 26, 2020 at 10:19

user330315

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

Collectives™ on Stack Overflow

Postgres DB constraint for a value of a column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related