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.
