3

I have a table reservation with columns roomno(INTEGER), startdate(DATE), enddate(DATE) with a primary key on (roomno, startdate).

How do I place a constraint on the table such that bookings are not allowed to overlap?

I am trying to implement this in SQLFIDDLE postgreSQL9.3

For example:

101  2016-01-01   2016-01-05
101  2016-01-03   2016-01-06 [This row should not be possible to insert]

startdate and enddate are data type date.

1
  • I took the liberty to fix contradicting columns names and reflected your comment in the question, where it's supposed to be. Jun 1, 2016 at 1:31

1 Answer 1

6

Use an exclusion constraint and be aware how bounds for the type daterange work. date is a discrete type, so ranges have default [) bounds. The manual:

The built-in range types int4range, int8range, and daterange all use a canonical form that includes the lower bound and excludes the upper bound; that is, [).

So, to book a room from 2016-01-01 to 2016-01-05, including first and last day, you have to specify bounds explicitly:

daterange(start_date, end_date, '[]')

Or you enter dates with exclusive upper bounds; to include 2016-01-05, enter 2016-01-06. Then you can use the simpler expression:

daterange(start_date, end_date)

Either create a functional index with the above expression in the exclusion constraint, like demonstrated by @ypercube:

This way you can keep columns like you have right now.

Or you can replace startdate and enddate with a daterange column and use the plain column in the exclusion constraint. Like in this related answer:

In any case, assuming roomno is an integer column, you need to install the additional module btree_gist first, once per database. See:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.