Exclusion Constraints in Postgres

How to Implement Accurate Booking in Reservation Systems?

Hotel Reservation

In PostgreSQL, exclusion constraints provide a way to ensure that certain combinations of column values are unique across rows. This is useful when you need to prevent conflicting or overlapping data, even if the individual columns can have duplicates.

An exclusion constraint works by specifying that two rows cannot exist if they meet a particular set of conditions. This is typically achieved using a GiST (Generalized Search Tree) or GIN (Generalized Inverted Index) index.

Use Cases for Exclusion Constraints:
- Avoid overlapping date range : Prevent two bookings from overlapping in time.
- Enforce geometric constraints: Ensure that geometric figures do not intersect each other.
- Prevent confliction values in complex data types: For instance, ensuring no two students are assigned the same task.

Let’s create a reservations table and see how we can prevent overlapping bookings.

Example: Room Reservations

CREATE TABLE reservations
(
 id serial PRIMARY KEY,
 room_id INTEGER,
 booking_status TEXT,
 start_date TIMESTAMP,
 end_date TIMESTAMP,
 EXCLUDE USING GIST (TSRANGE(start_date, end_date) WITH &&)
);

Explanation:
The EXCLUDE USING GISTclause creates an exclusion constraint that ensures there are no conflicting bookings for the same date range. The TSRANGE(start_date, end_date) WITH && condition checks for overlapping date ranges.

Inserting a Booking
Let’s try to book a room for our next vacation:

INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025–01–08 14:00:00', '2025–01–15 12:00:00');

This works fine, and we have our first booking:

+--+-------+--------------+-------------------+--------------------------+
|id|room_id|booking_status|start_date         |end_date                  |
+--+-------+--------------+-------------------+--------------------------+
|1 |1      |CONFIRMED     |2025-01-08 14:00:00|2025-01-15 12:00:00       |
+--+-------+--------------+-------------------+--------------------------+

Trying to Insert an Overlapping Booking

Now, let’s try to book the same room for overlapping dates:

INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');

We get an error:

ERROR: conflicting key value violates exclusion constraint "reservations_tsrange_excl"
DETAIL: Key (tsrange(start_date, end_date)) = (["2025–01–12 14:00:00", "2025–01–18 12:00:00")) conflicts with existing key (tsrange(start_date, end_date)) = (["2025–01–08 14:00:00", "2025–01–15 12:00:00")).

This behavior ensures that overlapping bookings cannot be inserted, much like how you can’t book a hotel room that’s already occupied.

Booking a Different Room at the Same Time

Let’s try to book another room for the same period:

INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (2, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');

Oops! We get the same error:

ERROR: conflicting key value violates exclusion constraint "reservations_tsrange_excl"
DETAIL: Key (tsrange(start_date, end_date)) = (["2025–01–12 14:00:00", "2025–01–18 12:00:00")) conflicts with existing key (tsrange(start_date, end_date)) = (["2025–01–08 14:00:00", "2025–01–15 12:00:00")).

The Issue

The exclusion constraint currently applies to the whole table, meaning it checks for overlapping date ranges across all rooms. What we actually want is to allow bookings in different rooms for the same period. To fix this, we need to modify our exclusion constraint.

Solution: Add room_id to the Exclusion Constraint

By including the room_id in the exclusion condition, we ensure that the constraint only applies to the same room.

-- Enable the btree_gist extension (if not already enabled)
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- New table definition with room_id in the exclusion constraint
CREATE TABLE reservations
(
 id serial PRIMARY KEY,
 room_id INTEGER,
 booking_status TEXT,
 start_date TIMESTAMP,
 end_date TIMESTAMP,
 EXCLUDE USING GIST (room_id WITH =, TSRANGE(start_date, end_date) WITH &&)
);

Now, we can book two rooms for the same period:

- Booking two rooms for the exact period
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (2, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');

Result

The table now allows overlapping bookings in different rooms:

+--+-------+--------------+-------------------+--------------------------+
|id|room_id|booking_status|start_date         |end_date                  |
+--+-------+--------------+-------------------+--------------------------+
|1 |1      |CONFIRMED     |2025-01-08 14:00:00|2025-01-15 12:00:00       |
+--+-------+--------------+-------------------+--------------------------+
|2 |2      |CONFIRMED     |2025-01-08 14:00:00|2025-01-15 12:00:00       |
+--+-------+--------------+-------------------+--------------------------+

Partial Exclusion Constraints

Now let’s consider what happens when a booking is canceled. The CANCELED status means the room is no longer occupied and can be booked again.

- Booking a room
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');

 - Cancel the booking
UPDATE reservations SET booking_status = 'CANCELED' WHERE room_id = 1;

At this point, the reservation for room 1 has been canceled:

+--+-------+--------------+-------------------+--------------------------+
|id|room_id|booking_status|start_date         |end_date                  |
+--+-------+--------------+-------------------+--------------------------+
|1 |1      |CANCELED      |2025-01-08 14:00:00|2025-01-15 12:00:00       |
+--+-------+--------------+-------------------+--------------------------+

Trying to Book the Canceled Room Again

We can now insert a new booking for room 1 for the same period:

- Trying to book the same room and period again
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025–01–12 14:00:00', '2025–01–18 12:00:00');

Oopps! We get the error again.

ERROR: conflicting key value violates exclusion constraint "reservations_room_id_tsrange_excl" Detail: Key (room_id, tsrange(start_date, end_date))=(1, ["2025-01-12 14:00:00","2025-01-18 12:00:00")) conflicts with existing key (room_id, tsrange(start_date, end_date))=(1, ["2025-01-12 14:00:00","2025-01-18 12:00:00")).

To solve this error, Partial Exclusion Constraints come into play.

To fix this, we can define a partial exclusion constraint, which only applies to rows that are not canceled.

Defining the Partial Exclusion Constraint

A partial exclusion constraint is a constraint that applies only to a subset of rows in a table, based on a specific condition.
This is useful when you want to enforce the exclusion condition only under certain circumstances. In our case, apply the condition only if the booking is not Canceled.

This is how we can define Partial Exclusion Constraint.

EXCLUDE USING GIST (room_id WITH =,TSRANGE(start_date, end_date) WITH &&) 
WHERE (booking_status != 'CANCELED')

-- Extra Where () check added.

Full Table Definition:

CREATE TABLE reservations
(
    id             serial primary key,
    room_id        INTEGER,
    booking_status TEXT,
    start_date     TIMESTAMP,
    end_date       TIMESTAMP,
    EXCLUDE USING GIST (room_id WITH =,TSRANGE(start_date, end_date) WITH &&) WHERE (booking_status != 'CANCELED')
);

Testing Partial Exclusion

INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (1, 'CONFIRMED', '2025-01-12 14:00:00', '2025-01-18 12:00:00');

INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (2, 'CONFIRMED', '2025-01-12 14:00:00', '2025-01-18 12:00:00');

-- Cancel reservation for room_id = 2
UPDATE reservations SET booking_status = 'CANCELED' WHERE room_id = 2;

-- Inserting new reservation for exact room_id and period
INSERT INTO reservations (room_id, booking_status, start_date, end_date)
VALUES (2, 'CONFIRMED', '2025-01-12 14:00:00', '2025-01-18 12:00:00');

SELECT *
FROM reservations;

Now, everything works as expected!

+--+-------+--------------+-------------------+--------------------------+
|id|room_id|booking_status|start_date         |end_date                  |
+--+-------+--------------+-------------------+--------------------------+
|1 |1      |CONFIRMED     |2025-01-08 14:00:00|2025-01-15 12:00:00       |
+--+-------+--------------+-------------------+--------------------------+
|2 |2      |CANCELED      |2025-01-08 14:00:00|2025-01-15 12:00:00       |
+--+-------+--------------+-------------------+--------------------------+
|3 |2      |CONFIRMED     |2025-01-08 14:00:00|2025-01-15 12:00:00       |
+--+-------+--------------+-------------------+--------------------------+

Conclusion

By using exclusion constraints in PostgreSQL, we can enforce the right business rules directly in the database, ensuring that:

• No overlapping bookings occur for the same room.
• Overlapping bookings can be allowed for different rooms.
• Canceled bookings can be reused without violating the constraint.

Quick Recap:
- Exclusion Constraints: Prevent conflicts like overlapping data in tables.
- Partial Exclusion Constraints: Apply exclusion logic only to a subset of rows, based on a condition (such as booking_status != 'CANCELED').

In conclusion, exclusion constraints are a powerful tool to ensure data integrity and enforce complex business logic. They allow us to maintain consistency while handling real-world scenarios, such as room bookings in hotels or scheduling systems.

If you found this post helpful, you may like my other posts:

• Enforcing Data Integrity with PostgreSQL Constraints
• PostgreSQL Domain Types and Enums: Ensuring Data Integrity
• The Evolution of Primary Keys in PostgreSQL: From Serial to Identity

Give a post a thumbs up and subscribe for more updates. Happy coding! 🧑‍💻👩‍💻👨‍💻