May 17, 2021

Sometimes when working with databases, we need to represent a range of values. For example, we might want to define a set of available numbers or a range of dates. One way to do that would be to create two columns that hold the bound values.

Above, we are using the timestamp with timezone column type. If you want to know more, check out Managing date and time with PostgreSQL and TypeORM

While this approach would work, it might not be very straightforward to work with. Also, it doesn’t ensure data integrity.

Defining range columns in PostgreSQL

Fortunately, Postgres has various built-in range types that can make working with ranges a lot easier. When choosing one of the range types, we need to take into account what values we want to store. To represent a range of timestamps with timezones, we need the tstzrange type.

With ranges, we have the lower bound and the upper bound. Every value in between we consider to be in the range.

In the above query, we can notice that we specify our range between the [ and ) characters.

The square brackets – [ ] – represent inclusive bounds. This means that the bound value is included in the range. Therefore, our lower bound is inclusive. We can use the @> operator to check if our range contains a value.

The round brackets – ( ) represent exclusive bounds. Using them ensures that the bound value is not included in the range. Therefore, our upper bound is inclusive.

Constructor functions

Aside from defining the ranges in the above way, we can use constructor functions that every range type has:

• int4range() – range of integer,
• int8range() – range of bigint,
• numrange() – range of numeric,
• tsrange() – range of timestamp (without time zone),
• tstzrange() – range of timestamp (with time zone),
• daterange() – range of date.

We can also define new range types.

By default, it assumes an inclusive lower bound and an exclusive upper bound.

We can change the above behavior with a third argument specifying the upper and lower bounds.

Unbounded ranges

So far, all of our above ranges had defined bounds. A feature worth noting is that in PostgresSQL, we can define an unbounded range by passing null as the bound value.

We can take it even further by defining a range without any bounds. If we do that, PostgreSQL considers all values of the element type as included in the range.

Range operators

So far in this article, we’ve used the @> operator to check if a range contains a given element. Aside from that, there are more operators worth noting.

Let’s start by noticing that we can do more with the @> operator. We can use it to check if a range contains another range.

With <@ we can check if a rrange contains an element.

By using && we can see if ranges have points in common.

There are more operators that we can use with ranges. For a full list check out the official documentation.

Performance optimizations with indexes

The above operators might come in handy in some real-life situations. Let’s imagine we want to get a list of all events happening on a specific date. To do that, we can use the @> operator.

Unfortunately, determining if an element is in a range in multiple records might not be a very straightforward operation. If we plan on making the above query often, it might be a good idea to create an index.

If you want to know more about indexes, check out API with NestJS #14. Improving performance of our Postgres database with indexes

Across all types of indexes, the fitting one to use with ranges can be the Generalized Search Tree (GiST) index.

With GiST and SP-GiST indexes, we can accelerate queries containing a broad list of operators: =, &&, <@, @>, <<, >>, -|-, &<, and &>.

Using range types with TypeORM

To use range columns with TypeORM, we need to provide the @Column() decorator with the type property.

A crucial thing to note above is that the type of the date_range property is a string. Let’s dig deeper into why that’s the case.

TypeORM, in its dependencies, has the pg library. Unfortunately, it uses a very old version of pg-types. Two months ago, pg-types started supporting ranges with the postgres-range library. We could use it to parse our range manually from a string to an object containing many useful functions.

Unfortunately, the postgres-range library doesn’t have many weekly downloads as of now, but that would change if the pg library would start using a newer version of pg-types.

To create an instance of the above Event, we need to pass the date_range as a string.

In recent articles, we’ve looked into Prisma as an alternative to TypeORM. Unfortunately, Prisma doesn’t support range types as of now.

Using range operators with TypeORM

Unfortunately, TypeORM doesn’t have built-in support for range operators. To use them, we need to perform raw SQL queries.

Unfortunately, the above code creates an SQL injection possibility. To deal with it, we can create a parameterized query. In the example below, $1  is replaced with a value of the date.

Summary

In this article, we’ve gone through the range types in PostgreSQL. To do that, we’ve created the events table and worked both with the date and numeric ranges. We’ve also used various operators that allow us to manipulate the range data. Aside from working with pure PostgreSQL, we’ve also used range types with TypeORM.