PostgreSQL Range Data Type

Introduction to PostgreSQL Range Types

In PostgreSQL, Range is a data type that represents an interval of values ​​of some element type. For example, a range of integers can represent a range of ages of a group of people, and a range of times can represent a time period for which a conference room is reserved.

The range value is an interval value, respresent by [, ], (, 和 ):

• The [ indicates the start of the range, including the starting value.
• The ] indicates the end of the range, including the end value.
• The ( indicates the start of the range, excluding the starting value.
• The ) indicates the end of the range, excluding the end value.

Here are some examples of ranges:

• [a,b] - Including values between a and b, including a and b.
• (a,b) - Including values between a and b, excluding a and b.
• [a,b) - Including values between a and b, including a, excluding b.
• (a,b] - Including values ​​between a and b, excluding a, including b.
• [a,) - Including a and values ​​greater than a.
• (a,) - Including values ​​greater than a.
• (,b] - Including b and values ​​less than b.
• (,b) - Including values ​​less than b.

PostgreSQL 14 introduced the Multirange type, which is a collection of Range types. The representation method is: { range_1, range_2, ...}.

Each Range type has its corresponding Multirange type.

PostgreSQL built-in Range Types

The following table shows the range types built into PostgreSQL:

PostgreSQL Range Operators and Functions

The following table shows some PostgreSQL operators for range types:

These operands are also suitable for Multirange type values.

At the same time, PostgreSQL provides many useful functions for Range and Multirange data types, as follows:

• The isempty() function checks whether a given range of values ​​is empty.
• The lower_inc() function checks whether the lower bound of a given range is inclusive.
• The lower_inf() function checks whether the lower bound of a given range is infinite.
• The lower() function returns the lower limit of a given range or range of multiple values.
• The multirange() function returns a multivalued range containing the given range.
• The range_merge() function computes the smallest range that includes all ranges or the entire multivalued range.
• The unnest() function expands a multivalued range value into a collection of range values.
• The upper_inc() function checks whether the upper limit of a given range is inclusive.
• The upper_inf() function checks whether the upper limit of a given range is infinite.
• The upper() function returns the upper bound of a given range or a range of multiple values.

PostgreSQL Range Examples

Below are some examples of PostgreSQL Range and Multirange.

To check if a range contains another range, use the following statement:

SELECT '[1,9]'::int4range @> '[3,9]'::int4range;

 ?column?
----------
 t

To check if a range contains a value, use the following statement:

SELECT '[1,9]'::int4range @> 3;

 ?column?
----------
 t

To check if a Multirange contains a range , use the following statement:

SELECT '{[1,5], [7, 9]}'::int4multirange @> '[3,4]'::int4range;

 ?column?
----------
 t

Create Your Own Range

In addition to using PostgreSQL’s built-in range types, you can create your own range types using the CREATE TYPE statement.

Here is the syntax for creating a custom range type:

CREATE TYPE name AS RANGE (
    SUBTYPE = subtype
    [, SUBTYPE_OPCLASS = subtype_operator_class ]
    [, COLLATION = collation ]
    [, CANONICAL = canonical_function ]
    [, SUBTYPE_DIFF = subtype_diff_function ]
    [, MULTIRANGE_TYPE_NAME = multirange_type_name ]
)

Assuming that you want to create a range type of time (time) named my_time_range, use the following statement:

CREATE TYPE my_time_range AS RANGE (
    SUBTYPE = time
);

The following statement uses the range type my_time_range just created:

SELECT my_time_range('10:00:00','12:00:00') @> '11:00:00'::time;

 ?column?
----------
 t

Conclusion

In PostgreSQL, Range is a data type that represents an interval of values ​​of some type. The Multirange type is a collection of Range types.