No overlapping dateranges in Postgres

Lets say we have several rooms, and every room has reservations. We do not want the reservations to overlap, ever. Here’s how we add that check to a table in PostgreSQL.

The following will make sure no start_date and end_date overlaps, but completely ignores the check on the room for now.

CREATE TABLE room_reservation (
  room text,
  start_date date,
  end_date date,
  EXCLUDE USING GIST (daterange("start_date", "end_date", '[]') WITH &&));

To get the room into the mix we need the btree_gist extension.

CREATE EXTENSION btree_gist;

Now we can include the room, notice the new room WITH = inside the EXCLUDE:

CREATE TABLE room_reservation (
  room text,
  start_date date,
  end_date date,
  EXCLUDE USING GIST (room WITH =, daterange("start_date", "end_date", '[]') WITH &&));

If you’re wondering what the [] is about. It specifies the bounds. (), [], [), (]. In our case [] means we include both the lower and upper bound (start and end date) in the check.

If we would use [) instead then the start_date of another entry could be equal to the end_date of the other one.

This is a simplified take on it, check https://en.wikipedia.org/wiki/Interval_(mathematics) for a detailed look.

You could also use a Range-Type to simplify the EXCLUDE a bit, see https://www.postgresql.org/docs/current/static/rangetypes.html for more

In an Elixir migration do this

# Create the extension on up, drop it on down migrations
execute("CREATE EXTENSION IF NOT EXISTS btree_gist", "DROP EXTENSION IF EXISTS btree_gist")

# Create the constraint (with the name :cannot_overlap)
create(constraint(:reservations, :cannot_overlap, exclude: ~s|gist ("room" WITH =, daterange("start_date", "end_date", '[]') WITH &&)|))