CODEWITHSUNDEEP
sqlpostgresqlwhere-clauseunique-constraintunique-index
Alew
Alew

Reputation: 378

PostgreSQL. constraint allowing only one of many possible values

I have statuses like this: started,calculated,finished I need a constraint allowing only one NOT finished status in a table.

This is allowed:

+----+----------+
| id |  status  |
+----+----------+
|  1 | finished |
|  2 | finished |
|  3 | started  |
+----+----------+

+----+------------+
| id |   status   |
+----+------------+
|  1 | finished   |
|  2 | finished   |
|  3 | calculated |
+----+------------+

this is forbidden due to two not finished statuses:

+----+------------+
| id |   status   |
+----+------------+
|  1 | finished   |
|  2 | finished   |
|  3 | calculated |
|  4 | started    |
+----+------------+

Upvotes: 1

Views: 418

Answers (1)

GMB
GMB

Reputation: 222582

You can use a filtering unique index:

create unique index myindex
    on mytable ((1)) 
    where (status <> 'finished')

The trick is to pass a fixed value instead of a column name to the on clause of the index (we need two parentheses so Postgres evaluates this as an expression). This combines with a where clause that filters status other than "finished" to implement the logic you want.

Demo on DB Fiddle

Upvotes: 1

Related Questions