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

Temporal constraints with ranges

PostgreSQL 18 can use range and multirange columns in WITHOUT OVERLAPS keys and PERIOD foreign keys. See the guide to PostgreSQL temporal constraints for examples and restrictions.

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.