Placing a uniqueness constraint on a date range 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.
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:


Uniqueness constraint with date range


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:


Updating table of versioned rows with historical records in PostgreSQL


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

Related:


Combining separate ranges into largest possible contiguous ranges
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL