Enforce unique constraint on 1 of n values

Question

Let's say I have a table like this.

create table foo (
    id serial primary key,
    kind smallint not null check (kind >= 0 and kind < 3)
);

With that check constraint, Postgres will only allow kind to be an int between 0 and 2, inclusive. So something like this is valid.

id | kind
---------
1  | 0
2  | 0
3  | 1
4  | 1
5  | 2
6  | 2

However is there a way to say, "at most 1 row can have the value 2"?

time=1
id | kind
---------
1  | 0
2  | 0
3  | 1
4  | 1
5  | 2

time=2
id | kind
---------
1  | 0
2  | 2
3  | 1
4  | 0
5  | 0

time=3
id | kind
---------
1  | 0
2  | 1
3  | 1
4  | 0
5  | 0

Different rows should be allowed to have kind=2 at different points in time, but at most 1 row can have kind=2.

Note, the other values should be unrestricted. You can have as many kind=0s and kind=1s as you like.

Answer 1

You can create unique partial index:

create unique index on foo (kind) where kind=2;