Range Types

Built-in Range

The following built-in ranges are available:

• int4range: integer range.
• int8range: bigint range.
• numrange: numeric range.
• tsrange: range of timestamp without the time zone.
• tstzrange: range of timestamp with the time zone
• daterange: date range.

In addition, you can customize range types. For details, see CREATE TYPE.

Example

-- Create a table and insert data into the table.
gaussdb=# CREATE TABLE reservation (room int, during tsrange); 
gaussdb=# INSERT INTO reservation VALUES (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');  
-- Inclusion
gaussdb=# SELECT int4range(10, 20) @> 3;  
 ?column? 
----------
 f
(1 row)

-- Determine whether the two ranges overlap.
gaussdb=# SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);  
 ?column? 
----------
 t
(1 row)

-- Extract the upper bound.
gaussdb=# SELECT upper(int8range(15, 25));  
 upper 
-------
    25
(1 row)
-- Calculate the intersection.
gaussdb=# SELECT int4range(10, 20) * int4range(15, 25);  
 ?column? 
----------
 [15,20)
(1 row)
-- Determine whether the range is empty.
gaussdb=# SELECT isempty(numrange(1, 5)); 
 isempty 
---------
 f
(1 row)

-- Drop the table.
gaussdb=# DROP TABLE reservation;

See the complete list of operators and functions on a range type in Range Functions and Operators.

Including and Excluding Bounds

Each non-empty range has two bounds, a lower bound and an upper bound. All values between the upper and lower bounds are included in the range. An inclusion bound means that the bound value itself is included in the range, while an exclusion bound means that the bound value is not included in the range.

In the textual form of a range, the inclusion lower bound is expressed as "[", and the exclusion lower bound is expressed as "("; the inclusion upper bound is expressed as "]", and the exclusion upper bound is expressed as ")".

The lower_inc and upper_inc functions test the upper and lower bounds of a range value, respectively.

Infinite (Unbounded) Range

When the lower bound of a range is unbounded, it means that all values less than the upper bound are included in the range. Similarly, when the upper bound of a range is unbounded, all values greater than the lower bound are included in the range. When both the upper and lower bounds are unbounded, all values of the element type are considered within the range. When the upper bound and the lower bound of the range are not set, that is, the upper bound is positive infinity and the lower bound is negative infinity, the range is an infinite (unbounded) range.

Element types with the infinity values can be used as explicit bound values. For example, in the timestamp range [today, infinity) and [today, infinity], [today, infinity) indicates that the special timestamp values infinity is not included, and [today, infinity] indicates that the special timestamp value infinity is included.

The lower_inf and upper_inf functions test the infinite upper and lower bounds of a range, respectively.

Range Input/Output

Range input mode:

(lower-bound, upper-bound)
(lower-bound, upper-bound]
[lower-bound, upper-bound)
[lower-bound, upper-bound]
Empty

Range output mode:

[lower-bound, upper-bound)
Empty

() or [] indicates whether the upper and lower bounds are excluded or included, respectively. empty indicates an empty range (a range that does not contain vertices).

The value of lower-bound can be a valid input string of the subtype or null, indicating that there is no lower bound. Similarly, upper-bound can be a valid input string of the subtype or null, indicating that there is no upper bound.

Each bound value can be referenced using the quotation marks("") character. This is necessary if a bound value contains parentheses (), square brackets [], commas (,), quotation marks (""), or backslashes (\), because otherwise those characters will be considered part of the range syntax. To put the quotation mark or backslash in a referenced bound value, put a backslash in front of it (and a pair of double quotation marks in its quoted bound value represents one quotation mark character, which is similar to the single quotation mark rule in SQL character strings). In addition, you can avoid referencing or use backslash escapes to protect all data characters. Otherwise, the data characters will be used as part of the range syntax. If you want to write a bound value that is an empty string, write "", indicating infinite bounds.

Spaces are allowed before and after a range value, but any space between parentheses or square brackets is used as part of the upper or lower bound value (depending on the element type, the space may or may not represent a value).

Examples:

-- All values between 3 (included) and 7 (excluded) are included.
gaussdb=# SELECT '[3,7)'::int4range;  
 int4range 
-----------
 [3,7)
(1 row)
-- All values between 3 (excluded) and 7 (excluded) are included.
gaussdb=# SELECT '(3,7)'::int4range;  
 int4range 
-----------
 [4,7)
(1 row)
-- Only value 4 is included.
gaussdb=# SELECT '[4,4]'::int4range;  
 int4range 
-----------
 [4,5)
(1 row)
-- No value is included (and will be normalized to null).
gaussdb=# SELECT '[4,4)'::int4range; 
 int4range 
-----------
Empty
(1 row)

Constructing Range

Each range type has a constructor function with the same name. Using constructor functions is often more convenient than writing a range literal constant because it avoids extra references to bound values. Constructor functions receive two or three parameters. Two parameters form a range in the standard form, where the lower bound is included and the upper bound is excluded, and three parameters form a range according to the bound specified by the third parameter. The third parameter must be one of the following character strings: (), (], [], or [].

-- The complete format is: lower bound, upper bound, and textual parameters indicating the inclusion/exclusion of bounds.
gaussdb=# SELECT numrange(1.0, 14.0, '(]');  
  numrange  
------------
 (1.0,14.0]
(1 row)
-- If the third parameter is ignored, it is assumed to be '[)'.
gaussdb=# SELECT numrange(1.0, 14.0);  
  numrange  
------------
 [1.0,14.0)
(1 row)
-- Although '(]' is specified here, the value will be converted to the standard format when displayed, because int8range is a discrete range type.
gaussdb=# SELECT int8range(1, 14, '(]');  
 int8range 
-----------
 [2,15)
(1 row)
-- Using NULL for a bound causes the range to be unbounded on that side.
gaussdb=# SELECT numrange(NULL, 2.2); 
 numrange 
----------
 (,2.2)
(1 row)

Discrete Range

A range element type has a well-defined "step" such as integer or date. In these types, if there is no valid value between two elements, they can be said to be adjacent. This is in contrast to a continuous range in which other element values can always be identified between two given values. For example, a range above the numeric type is continuous, and the range of timestamp is also continuous. (Although timestamp has limited precision and can be considered as discrete in theory, it can be considered as continuous because the step is not normally considered.)

Another way to consider discrete range types is to have a clear "next" or "previous" value for each element value. You can switch between inclusion and exclusion expressions of a range bound by replacing it with the original given next or previous element value. For example, in an integer range type, [4,8] and (3,9) represent the same set of values, but not for numeric ranges.

A discrete range type should have a regularization function that knows the specified step size of the element type. The regularization function can convert the equivalents of the range type to the same expression, in particular consistent with the inclusion or exclusion bounds. If you do not specify a regularization function, ranges with different formats will always be considered as unequal, even if they actually express the same set of values.

The built-in range types int4range, int8range, and daterange use a regularized form that includes the lower bound and excludes the upper bound, that is, [). However, user-defined range types can use other conventions.

Index

In addition, B-tree indexes can be created on table columns of the range type. For these index types, basically the only useful range operation is equivalence. Using the corresponding < and > operators, there is a B-tree sort order for range value definitions, but that order is fairly arbitrary and is often less useful in the reality. The B-tree support for range types is primarily designed to allow sorting within a query, rather than creating an index.