Place complex uniqueness constraint in Postgres?

user_3224967 0
Viewed 810

In Postgres, I have a table user_badge containing three columns, appearing as something like the following.

# select * from user_badge;
 id | user_id | badge_id | is_active |
----+---------+----------+------------
 1    bob       00001      False
 2    bob       00002      True
 3    alice     00003      True
 4    alice     00004      False
 5    alice     00005      False

In this table, a user can have more than one badge, but each user must not have more than one active badge (i.e. row in which the is_active column is True).

Another way of saying this, is that the query

SELECT user_id FROM user_badge WHERE is_active;

must return unique rows.

This is a constraint I wish to place upon this table. Is there a way of specifying Postgres to guarantee it? In Postgres's documentation on constraints I see how can one can place a uniqueness constraint on a column and on a set of columns, but do not see examples where the uniqueness constraint involves a WHERE.

constraints postgresql
1 Answers

Use a partial or filtered index:

create unique index unq_user_badge_user_is_active
    on user_badge(user) where is_active;

This is described in the documentation in the section on partial indexes.

user_11440350
Related