How to add a constraint that's partial and also works with overlapping daterange for postgres?

Question

I am using a postgres with daterange field called valid_period in thing_thing table.

The query I use to add the constraint

CREATE EXTENSION IF NOT EXISTS btree_gist;

ALTER TABLE thing_thing 
ADD CONSTRAINT prevent_overlapping_valid_periods_by_team 
        EXCLUDE USING gist(team_id WITH =, valid_period WITH &&) 
        DEFERRABLE INITIALLY DEFERRED;

This works well.

• row with same team_id and overlapping valid_period => blocked 🛑
• row with different team_id and overlapping valid_period => allowed ✅
• row with same team_id and NOT overlapping valid_period => allowed ✅

What I need but not sure how to do

But I only want the constraint to kick in IF

• the field is_removed (a boolean field) is also false OR
• the field state (a varchar field) contains one of any possible values such as CANCELLED, REJECTED

So the following are not working:

• row with same team_id and overlapping valid_period but existing row is_removed is True => expected allowed ✅ but blocked 🛑
• row with same team_id and overlapping valid_period but existing row state is CANCELLED => expected allowed ✅ but blocked 🛑
• row with same team_id and overlapping valid_period but existing row state is REJECTED => expected allowed ✅ but blocked 🛑

How do I alter the constraints to allow the exceptions? In other words, a bit like partial unique index.

I am a bit pessimistic because I understand there's no such thing as a partial constraint from another SO answer here

But at the same time, I think not possible to use partial unique index to prevent illegal overlapping daterange.

Answer 1

You can use a partial index with this - at least with PostgreSQL v13 you can - haven't checked the others yet.

Note the parentheses on the WHERE condition though - it protests if you forget those.

The example below is slightly simplified from your description, but logically the same.

=> CREATE TABLE teams (team_id int not null, valid_period daterange not null, is_removed boolean not null, state char not null);

=> ALTER TABLE teams ADD CONSTRAINT no_overlaps 
    EXCLUDE USING gist(team_id WITH =, valid_period WITH &&)
    WHERE (is_removed = false OR state IN ('C','R'));

=> INSERT INTO teams VALUES (1, daterange('2020-01-01', '2021-01-01'), false, 'X');
INSERT 0 1

=> INSERT INTO teams VALUES (1, daterange('2020-01-01', '2021-01-01'), false, 'X');
ERROR:  conflicting key value violates exclusion constraint "no_overlaps"
DETAIL:  Key (team_id, valid_period)=(1, [2020-01-01,2021-01-01)) conflicts with existing key (team_id, valid_period)=(1, [2020-01-01,2021-01-01)).

=> INSERT INTO teams VALUES (1, daterange('2020-01-01', '2021-01-01'), true, 'X');
INSERT 0 1

=> INSERT INTO teams VALUES (1, daterange('2020-01-01', '2021-01-01'), true, 'C');
ERROR:  conflicting key value violates exclusion constraint "no_overlaps"
DETAIL:  Key (team_id, valid_period)=(1, [2020-01-01,2021-01-01)) conflicts with existing key (team_id, valid_period)=(1, [2020-01-01,2021-01-01)).