MULTIRANGES 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 feature of PostgreSQL 14, has significantly increased the power of this feature. 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 an easy illustration:

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

The important thing to note here is that ranges can be formed on the fly. In this case I’ve created an int4 range. The ranges are 10 (which is included) through 20 (which is no longer included). Basically, every numeric data type that can be sorted can be turned into a range.

It’s crucial to note that PostgreSQL will guarantee that the range is, in fact, valid, which necessitates that the upper bound must be greater than the lower bound and so forth:

 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. This is how it goes:

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

You can see from the above that 17 is indeed between 10 and 20 in the table. Though it’s a straightforward illustration, this is also very helpful in more intricate situations.

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 beauty here is that the operators are available are consistent and work the same way for all range types. 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, the complexity is greatly reduced by the automatic folding of different ranges into a collection of distinct parts.

A multirange can also be used as a data type and stored inside a table just like any other value:

 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

The way you query this type of column is pretty straightforward and works as follows:

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

You can simply apply the operator on the column, and you’re good to do. In our example, one row is returned which is exactly what we expect.

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’s an illustration:

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

This scale goes 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

That’s not all: you can perform basic operations using these ranges. Now just imagine what it would take to do such calculations by hand in some 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 next example shows how intersections can be calculated:

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

You can use a variety of other operations of this type to deal with ranges. The PostgreSQL documentation contains a comprehensive list of all available functions and operators.

Multiranges: aggregating ranges

The final topic I want to present is how to aggregate ranges into bigger blocks. Often, data is available in a standard form, and you want to do something fancy with it. Build 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.

You can now use the range_agg function to form 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)

Think about the following: In essence, aggregation and unnesting let you respond to inquiries like, “How many uninterrupted periods of activity did we see?” If you are, for instance, managing data warehouses, that can be really helpful.

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.​