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.

Traditionally, when dealing with events or periods, developers often use two separate columns to represent the start and end of a range. For example:

While this approach works, it has a few drawbacks:

1. Querying Complexity: Writing queries to find overlapping events or events within a specific period becomes complex and error-prone.
2. Data Integrity: Ensuring that reservations do not overlap is difficult.

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:

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:

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.

With the above constraint, the second insert on the following SQL statements fails because the duration overlaps with the first insert.

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.

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.

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.

And that is how to create an air-tight table that holds reservations.

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.

• Watch the video guide for range columns
• Check constraint video
• Self-host Maps on Supabase Storage with Protomaps
• Row Level Security guide