Postgresql Exclude constraint on a set of values

Question

Suppose I have a table with two columns:

| id | status |

Status can take 3 values: booked, started, completed

I need a constraint that will satisfy the following requirement:

For entire set of rows, only one record can be in non completed state: booked or started.

I believe I need an Exclude constraint for that, but I don't know what operator can be specified:

add constraint constraint_x exclude (status with ANY?) where (status <> 'Completed')

That's the solution I came up to:

add constraint constraint_x exclude ((1) with =) where ((status) <> 'Completed')

Is this a good one?

Laurenz Albe · Accepted Answer · 2019-08-16 09:52:41Z

Your solution is ok.

I would solve that with a partial unique index like this:

CREATE UNIQUE INDEX ON table_name ((1)) WHERE status <> 'Completed';

Dino · Accepted Answer · 2019-08-16 09:56:55Z

0

That's the solution I came up to:

add constraint constraint_x exclude ((1) with =) where ((status) <> 'Completed')

8,37213 gold badges50 silver badges93 bronze badges

answered Aug 16, 2019 at 9:35

719 bronze badges

2 Answers 2