1

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.

Improve this question

1 Answer 1

Reset to default
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)).
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Thank you, Richard. I am using postgres 10. I will check if it works there. By the way, I check the docs for 13 but I cannot find the docs stating what you said was explicitly possible. either at postgresql.org/docs/13/sql-altertable.html or postgresql.org/docs/13/ddl-constraints.html if you do know where it exists at the docs can point for me? I want to learn what the docs say about this if possible
thank you, it does work in postgres 10 as well. I am surprised by the SO answer I found that says this is conditional constraints are not possible. Weird.
I wasn't sure whether it was possible or not, so checked \h alter table in psql (postgresql.org/docs/current/sql-altertable.html) and saw in the table_constraint section EXCLUDE ... index_parameters [ WHERE ( predicate ) ]. Good to know it's been supported for a few versions. Thanks.
Thank you. Just wanna say that I still find that page hard to read... maybe it’s me 🤷🏻‍♂️
Sorry I realized the constraint will also block if I change existing rows. Was wondering if you have a solution for this? stackoverflow.com/questions/64586618/…

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.