Exclusion constraint that allows overlapping at the boundaries

Question

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?

score 1 · Accepted Answer · answered Apr 05 '19 at 19:45

1

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.

answered Apr 05 '19 at 19:45

Laurenz Albe

209,280
17
206
263

Thank you very much for the solution, and also for fixing the question description – son hoang Apr 06 '19 at 09:59

Exclusion constraint that allows overlapping at the boundaries

1 Answers1