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!

So you are using week numbers? If so, there can be no overlap between dates of weeks. — Patrick
– Patrick, Commented Jun 13, 2016 at 13:28

Nick Barnes · Accepted Answer · 2016-06-13 13:33:39Z

10

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 &&)
)

answered Jun 13, 2016 at 13:33

Nick Barnes

21.8k5 gold badges64 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Trung Tran Over a year ago

Thanks - to confirm, would this allow overlap of the end of week_start with the beginning of week_end? For example, 6/1 - 6/3 and 6/3 - 6/4 should be allowed.

Nick Barnes Over a year ago

@user1547174: Yes, the daterange() function includes the lower bound and excludes the upper bound.

Trung Tran Over a year ago

Gotcha - I tried the command alter table workouts add constraint EXCLUDE USING gist (daterange(week_start, week_end) WITH &&); but I'm getting ERROR: syntax error at or near "USING"... Could you please let me know what the syntax error might be?

Nick Barnes Over a year ago

@user1547174: You need to either give the constraint a name (ADD CONSTRAINT c EXCLUDE ...) or drop the CONSTRAINT keyword altogether (ADD EXCLUDE ...)

Nick Barnes Over a year ago

@Adzz: Actually, yes, it looks like there's a known issue which causes simultaneous overlapping inserts to throw deadlock errors instead of constraint violation errors. This shouldn't really matter for most use cases - it was going to fail anyway, just with a different error message - but it could be a real problem if you need high throughput and you expect a lot of violations (as there is some delay before the deadlock detection kicks in).

|

Patrick · Accepted Answer · 2016-06-13 13:34:39Z

1

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 &&)
);

answered Jun 13, 2016 at 13:34

Patrick

33k7 gold badges73 silver badges102 bronze badges

Collectives™ on Stack Overflow

Prevent date overlap postgresql

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related