0

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.

2
  • Your example is invalid. Is the column named "kind", or "status"?
    – jjanes
    Oct 11, 2019 at 1:27
  • Bah. Sorry, typo. Took that from a different file.
    – 425nesp
    Oct 11, 2019 at 1:31

1 Answer 1

1

You can create unique partial index:

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.