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.

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

You can create unique partial index:

create unique index on foo (kind) where kind=2;
|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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