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 | +----+------------+
Advertisement
Answer
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.