Prevent date overlap postgresql

Question

Is there a way to add a constraint to a postgresql table to prevent dates overlapping? For example, I have a table called workouts that has date columns week_start, week_end. I want to make sure that none of the week_start - week_end ranges overlaps with any existing ranges. HOWEVER, the end date of week_start can overlap with the start date of week_end.

Can someone help?

Thanks in advance!

Answer 1

You can do this with an exclusion constraint, using the overlap operator (&&) for the daterange type:

CREATE TABLE workouts (
  week_start DATE,
  week_end DATE,
  EXCLUDE USING gist (daterange(week_start, week_end) WITH &&)
)

Answer 2

You can add an EXCLUDE table constraint to your table definition and then work with ranges to detect overlaps. This would work really nice if you can change your table definition to turn columns week_start and week_end into a single range, say weeks.

CREATE TABLE workouts (
  ...
  weeks   intrange
  EXCLUDE USING gist (weeks WITH &&)
);