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.

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

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

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, if you want 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 make it 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:

Either way, assuming roomno is an integer column, you need to install the additional module btree_gist first, once per database.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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