How to ensure only a single row with a certain value

Question

Given this table:

CREATE TABLE rollouts
(
 id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
 device_id TEXT NOT NULL,
 is_active BOOLEAN NOT NULL DEFAULT FALSE
);

How can I ensure that for a given device_id, there is only ever one active rollout? Multiple inactive ones are fine, so a simple unique index doesn't cut it.

Answer 1

You can use a partial unique index:

create unique index on rollouts (device_id) 
where is_active;

• Michael Böckling: Thanks, will try that out.