Multiples in PostgreSQL 14

Understanding Multiranges

The ability to store different types of intervals with upper and lower bounds using range types has been a feature of PostgreSQL for quite some time. Multiranges, a significant new addition to the database in PostgreSQL 14, makes this feature even more potent. I have put together a brief introduction outlining the fundamental ideas and key characteristics of multiranges to assist you in understanding them.

Getting started with range types

I’d like to quickly go over the fundamental range types before we get into multiranges so you can compare what was previously available with what is currently available. Here’s a simple example:

 test=# SELECT int4range(10, 20); int4range ----------- [10,20) (1 row)

It’s important to remember that ranges can be made on the spot in this situation. I’ve created an int4 range in this instance. The ranges are 10 (which is included) through 20 (which is no longer included). Basically, a range can be created from any type of numeric data that can be sorted.

It’s important to remember that PostgreSQL will make sure the range is valid, which means that the upper bound and lower bound must be higher than each other:

 test=# SELECT int4range(432, 20); ERROR: range lower bound must be less than or equal to range upper bound

What is the purpose of a range type in the first place?

Well, PostgreSQL offers a wide range of operators in addition to validation. A way to determine whether a value is inside or outside of a range is one of the fundamental operations you’ll frequently need. Here’s how it works:

 test=# SELECT 17 <@ int4range(10, 20); ?column? ---------- t (1 row)

You can see from the above that 17 is indeed between 10 and 20 in the table. It’s a simple example, but this is also very useful in more complex cases.

Ranges can also be used as data types that can be included in tables:

 test=# CREATE TABLE t_range (id serial, t tstzrange); CREATE TABLE test=# INSERT INTO t_range (t) VALUES ('["2022-01-01", "2022-12-31"]') RETURNING *; id | t ----+----------------------------------------------------- 1 | ["2022-01-01 00:00:00-05","2022-12-31 00:00:00-05"] (1 row) INSERT 0 1

In this instance, I successfully inserted a value using the tstzrange (“timestamp with time zone range”) function.

The great thing about this is that all range types have consistent, readily available operators. This makes them relatively easy to use.

Multiranges: Range types of steroids

What then are multiranges? Simple is the idea: Ranges are compiled into a multirange. You can store as many pairs as you like in place of just two values. Let’s look at a real-world illustration:

 test=# SELECT int4multirange (int4range (10, 20), int4range (40, 50)); int4multirange ------------------- {[10,20),[40,50)} (1 row)

Although the multirange in this example was spontaneously created using two ranges, you can cram in as many value pairs as you like:

 test=# SELECT int4multirange( int4range(10, 20), int4range(40, 50), int4range(56, 62), int4range(80, 90) ); int4multirange ----------------------------------- {[10,20),[40,50),[56,62),[80,90)} (1 row)

Ranges can be created using PostgreSQL’s really nice features, as can be seen in the following example:

 test=# SELECT int4multirange( int4range(10, 20), int4range(12, 25), int4range(38, 42) ); int4multirange ------------------- {[10,25),[38,42)} (1 row)

As you can see, various ranges are folded to create a collection of distinct parts. This happens automatically and greatly reduces complexity.

Similar to how any other value is stored in a table, a multirange can also be used as a data type:

 test=# CREATE TABLE t_demo (id int, r int4multirange); CREATE TABLE test=# INSERT INTO t_demo SELECT 1, int4multirange( int4range(10, 20), int4range(12, 25), int4range(38, 42)) RETURNING *; id | r ----+------------------- 1 | {[10,25),[38,42)} (1 row) INSERT 0 1

This type of column’s querying process is fairly simple and goes like this:

 test=# SELECT * FROM t_demo WHERE 17 <@ r; id | r ----+------------------- 1 | {[10,25),[38,42)}

In our example, one row is returned, as expected, so you can just apply the operator to the column and you’re good to go.

Handling “infinity” inside ranges

You’ve seen that ranges have a start and an end, but there’s more to it than that.

In PostgreSQL, a range is aware of the concept of “infinity”.

Here is an example:

 test=# SELECT numrange(NULL, 10); numrange ---------- (,10) (1 row)

The range in question extends from -INFINITY to 10 (not included).

Once more, you can combine separate ranges into a single block:

 test=# SELECT numrange(NULL, 10), numrange(8, NULL); numrange | numrange ----------+---------- (,10) | [8,) (1 row) test=# SELECT nummultirange( numrange(NULL, 10), numrange(8, NULL) ); nummultirange --------------- {(,)} (1 row)

The example displays a range that encompasses all numbers (= – INFINITY to +INFINITY), as shown by the following example:

 test=# SELECT 165.4 <@ nummultirange( numrange(NULL, 10), numrange(8, NULL) ); ?column? ---------- t (1 row)

The conclusion is accurate: the number 165.4 does fall within the acceptable range.

Performing basic calculations

Additionally, you can use these ranges to carry out simple operations. Now consider the time required to perform such calculations manually in a given application. It would be slow, error-prone and in general, pretty cumbersome:

 test=# SELECT nummultirange(numrange(1, 20)) - nummultirange(numrange(4, 6)); ?column? ---------------- {[1,4),[6,20)} (1 row)

A multirange, which represents two ranges separated by a space, can be made by subtracting two ranges from one another. The following illustration demonstrates how to calculate intersections:

 test=# SELECT nummultirange(numrange(1, 20)) * nummultirange(numrange(4, 6)); ?column? ---------- {[4,6)} (1 row)

To deal with ranges, you can use a number of other operations of this kind. An exhaustive list of all functions and operators offered by PostgreSQL can be found in its documentation.

Multiranges: aggregating ranges

I’d like to discuss how to combine smaller blocks of ranges as my final topic. The desire to do something fancy with data that is available in a standard form is common. Create a table first:

 test=# CREATE TABLE t_holiday (name text, h_start date, h_end date); CREATE TABLE test=# INSERT INTO t_holiday VALUES ('hans', '2020-01-10', '2020-01-15'); INSERT 0 1 test=# INSERT INTO t_holiday VALUES ('hans', '2020-03-04', '2020-03-12'); INSERT 0 1 test=# INSERT INTO t_holiday VALUES ('joe', '2020-01-04', '2020-03-02'); INSERT 0 1

Note that data is stored in the conventional manner (“from / to”) in the table I’ve created and the three rows I’ve added.

Using the range agg function, you can now construct ranges instantly and produce a multirange:

test=# SELECT range_agg(daterange(h_start, h_end)) FROM t_holiday; range_agg --------------------------------------------------- {[2020-01-04,2020-03-02),[2020-03-04,2020-03-12)} (1 row) 

It’s important to note that you can unnest a data type like this and turn it into single entries in addition to creating a multirange:

 test=# SELECT x, lower(x), upper(x) FROM unnest((SELECT range_agg(daterange(h_start, h_end)) FROM t_holiday)) AS x; x | lower | upper -------------------------+------------+------------ [2020-01-04,2020-03-02) | 2020-01-04 | 2020-03-02 [2020-03-04,2020-03-12) | 2020-03-04 | 2020-03-12 (2 rows)

Let’s think about this query: Basically, aggregation and unnesting allow you to answer questions such as: “How many continuous periods of activity did we see?” That can be really useful if you are, for example, running data warehouses.

Finally…

Multiranges are, in general, a really useful addition to what PostgreSQL already has to offer. PostgreSQL v14’s new feature enables us to offload a number of tasks to the database that would have otherwise been difficult to complete and difficult to implement on your own.

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It proactively identifies root causes of complex business-impacting database scalability and performance issues across a growing number of clouds, RDBMS, NoSQL, and machine learning database platforms.​