When working on applications such as a reservation app or calendar app, you need to store the start time and end time of an event. You may also need to query events occurring in a specific time frame or ensure that certain events do not overlap. If you have a table with two separate columns start_at and end_at to hold the beginning and end of an event, it might be hard to perform advanced queries or add constraints to prevent overlaps. This article will show how range-type columns could provide helpful query functionalities and advanced constraints to avoid overlapping.
The Problem with Traditional Date Columns
Traditionally, when dealing with events or periods, developers often use two separate columns to represent the start and end of a range. For example:
_10create table reservations (_10 id serial primary key,_10 title text,_10 start_at timestamptz,_10 end_at timestamptz_10);
While this approach works, it has a few drawbacks:
- Querying Complexity: Writing queries to find overlapping events or events within a specific period becomes complex and error-prone.
- Data Integrity: Ensuring that reservations do not overlap is difficult.
Enter Range Types
Range types are data types in Postgres that hold the beginning and end of a range of a base type. The range of int4 is int4range, the range of timestamptz is tstzrange, and the range of date is daterange. Each range has a start value, an end value, and either square brackets [] or parenthesis () surrounding them. A bracket means the end is inclusive, and a parenthesis means the end is exclusive. An int4range of [2,5) represents a range of integers from 2 including it to 5 excluding it, so 2, 3, and 4.
Querying range columns
Using these range values, we can create a reservation table like the following:
_10create table reservations (_10 id serial primary key,_10 title text,_10 duration tstzrange_10);
Using tstzrange instead of two timestamptz columns have a few advantages. First, it allows us to easily query reservations that overlap with a provided range using the && operator. Look at the following select query:
_10select *_10 from reservations_10 where duration && '[2024-07-04 16:00, 2024-07-04 19:00)';
This query returns rows where the duration overlaps with [2024-07-04 16:00, 2024-07-04 19:00). For example, a row with [2024-07-04 18:00, 2024-07-04 21:00) will be returned, but a row with [2024-07-04 20:00, 2024-07-04 22:00) will not be returned. The overlaps operator can be used when finding reservations or events in a given period.
Postgres provides more range-specific operators. The official Postgres documentation provides a complete list of range operators.
Adding constraints on range columns
When working on a reservations app, you might want to ensure there are no overlapping reservations. Range columns make it easy to add such constraints. The following SQL statement adds an exclude constraint that prevents new inserts/ updates from overlapping on any of the existing reservations.
_10alter table reservations_10 add constraint exclude_duration exclude_10 using gist (duration with &&)
With the above constraint, the second insert on the following SQL statements fails because the duration overlaps with the first insert.
_10-- Add a first reservation_10insert into reservations (title, duration)_10values ('Tyler Dinner', '[2024-07-04 18:00, 2024-07-04 21:00)');_10_10-- The following insert fails because the duration overlaps with the above_10insert into reservations (title, duration)_10values ('Thor Dinner', '[2024-07-04 20:00, 2024-07-04 22:00)');
Now, the exclusion constraint prevents any reservations from overlapping, but in the real world, a single reservations table typically holds reservations for different restaurants and tables within a restaurant, and just because a single reservation was made at a restaurant, it does not mean the entire restaurant is booked. Postgres can create such constraints where an insert or an update is disallowed only if a specific other column matches and the range overlaps.
Let’s say we had a table_id column in our reservations table. This table_id could represent a single table in various restaurants this database holds.
_10create table reservations (_10 id serial primary key,_10 title text,_10 table_id int4,_10 duration tstzrange_10);
With a table_id column in place, we can add a constraint to ensure that reservations on the same table do not overlap. The constraint requires the btree_gist extension.
_10-- Enable the btree_gist index required for the constraint._10create extension btree_gist_10_10-- Add a constraint to prevent overlaps with the same table_id_10alter table reservations_10 add constraint exclude_duration_10 exclude using gist (table_id WITH =, duration WITH &&);
With this simple constraint, no two reservations will overlap with each other with the same table_id. If we run the following inserts, the second insert will fail because it is trying to book the same table as the first insert while the duration overlaps.
_11-- Add a first reservation_11insert into reservations (title, table_id, duration)_11values ('Tyler Dinner', 1, '[2024-07-04 18:00, 2024-07-04 21:00)');_11_11-- Insert fails, because table 1 is taken from 18:00 - 21:00_11insert into reservations (title, table_id, duration)_11values ('Thor Dinner', 1, '[2024-07-04 20:00, 2024-07-04 22:00)');_11_11-- Insert succeeds because table 2 is not taken by anyone_11insert into reservations (title, table_id, duration)_11values ('Thor Dinner', 2, '[2024-07-04 20:00, 2024-07-04 22:00)');
And that is how to create an air-tight table that holds reservations.
Conclusion
Postgres's range columns offer a solution for handling range data in applications like reservation systems. They simplify queries with specific operators such as && and improve data integrity by enabling constraints to prevent overlaps. Range columns provide an alternative to traditional two-column approaches for representing periods. By leveraging these features, developers can create more sophisticated and reliable applications with less code.