Search code examples
postgresqloverlapexclusion-constraint

Exclusion constraint that allows overlapping at the boundaries

I tried to have a PostgreSQL constraint so that there will be no overlap between two date intervals. My requirement is that the date c_from for one entry can be the same as c_until for another date.

Eg: "01/12/2019 12/12/2019" and "12/12/2019 31/21/2019" are still date ranges that do not conflict. I have "[]" in my query but it seems not to work.

user_no   INTEGER NOT NULL REFERENCES usr,
c_from      DATE DEFAULT NOW(),
c_until     DATE DEFAULT 'INFINITY',
CONSTRAINT unique_user_per_daterange EXCLUDE USING gist (user_no WITH =, daterange(c_from, c_until, '[]') WITH && )

When I have the date range above, I get this error: 

(psycopg2.IntegrityError) conflicting key value violates exclusion constraint "unique_user_per_daterange"

Could you please help?

Solution

  • Use ranges that do not include one of the ends:

    daterange(c_from, c_until, '[)')

    Then they won't conflict, even if one interval ends at the same point where another begins.