Prevent overlapping date intervals in Postgres with a room booking example

I recently ran into this issue when designing the subscription system for moddox.io. I needed to prevent a customer from having two active subscriptions at the same time. This is already checked at the application level, but I wanted the database to ensure it as well.

In this post I’m using a more trivial example; rooms and room bookings, and creating a constraint that prevents a room from being booked twice for the same night.

CREATE TABLE rooms (
  room_id SERIAL PRIMARY KEY,
  room_name VARCHAR(255),
  deleted_at TIMESTAMP WITHOUT TIME ZONE
);

CREATE TABLE room_bookings (
  booking_id SERIAL PRIMARY KEY,
  room_id INT NOT NULL REFERENCES rooms,
  check_in DATE NOT NULL,
  check_out DATE NOT NULL,
  created_at TIMESTAMP WITHOUT TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
  deleted_at TIMESTAMP WITHOUT TIME ZONE
);

Note that SERIAL is shorthand for INTEGER DEFAULT NEXTVAL('tablename_colname_seq') NOT NULL which is an auto-incremented integer for every inserted row

Adding the constraint

Before creating the constraint, you must enable the btree_gist extentions. This needs to be done once per database:

CREATE EXTENSION btree_gist;

Below is the constraint that ensures that no bookings are overlapping for the same room. It does however only apply if the bookings are not soft deleted, i.e, have a value in the deleted_at column.

ALTER TABLE room_bookings 
ADD CONSTRAINT prevent_double_room_bookings EXCLUDE USING gist
(
  room_id WITH =, 
  daterange(check_in, check_out, '[)') WITH &&
) WHERE (deleted_at IS NULL);

Since the date range is defined with '[)' and not '[]', one booking may exist with check_in on the same day that another booking has check_out. This has to do with inclusive and exclusive bounds when using ranges in Postgres. The lower bound in our example is inclusive, while the upper bound is exclusive.

Testing the constraint

Create a room to book. The room automatically received the id 1.

INSERT INTO rooms (room_name) VALUES ('Our cozy room');

Create a booking for our cozy room, three nights:

INSERT INTO room_bookings (
    room_id, 
    check_in, 
    check_out
  ) VALUES (
    1, 
    CURRENT_DATE, 
    CURRENT_DATE + 3
  );

If you run this statement again, you will receive this message which means that the constraint is working:

ERROR:  conflicting key value violates exclusion constraint "prevent_double_room_bookings"
DETAIL:  Key (room_id, daterange(check_in, check_out, '[)'::text))=(1, [2022-01-12,2022-01-15)) conflicts with existing key (room_id, daterange(check_in, check_out, '[)'::text))=(1, [2022-01-12,2022-01-15)).
SQL state: 23P01

Also, if I modify the insert statement, to book the room the same day as the checkout of the previous booking, it works:

INSERT INTO room_bookings (
    room_id, 
    check_in, 
    check_out
  ) VALUES (
    1, 
    CURRENT_DATE + 3, 
    CURRENT_DATE + 4
  );

Giving us two rows in our room_bookings table:

While we are at it, we can also add a check-constraint to check that no room bookings have check_out before check_in which wouldn’t make sense:

ALTER TABLE room_bookings 
ADD CONSTRAINT check_check_in_is_before_check_out 
CHECK (
	check_in < check_out
);

Now, if we try to add a row with check_in that is after check_out, we get this error:

ERROR:  new row for relation "room_bookings" violates check constraint "check_check_in_is_before_check_out"
DETAIL:  Failing row contains (5, 1, 2022-01-20, 2022-01-19, 2022-01-12 11:29:34.85068, null).
SQL state: 23514

That’s it for today. Let me know if you appreciate this post and want to see more on this subject.