Range types v15

Overview

Range types are data types representing a range of values of some element type (called the range's subtype). For instance, ranges of timestamp might be used to represent the ranges of time that a meeting room is reserved. In this case, the data type is tsrange (short for timestamp range), and timestamp is the subtype. The subtype must have a total order so that it's well-defined whether element values are within, before, or after a range of values.

Range types are useful because they represent many element values in a single range value and because concepts such as overlapping ranges can be expressed clearly. The use of time and date ranges for scheduling purposes is the clearest example. However, price ranges, measurement ranges from an instrument, and so forth can also be useful.

Built-in range types

PostgreSQL comes with the following built-in range types:

• int4range — Range of integer.
• int8range — Range of bigint.
• numrange — Range of numeric.
• tsrange — Range of timestamp without time zone.
• tstzrange — Range of timestamp with time zone.
• daterange — Range of date.

In addition, you can define your own range types. See the PostgreSQL documentation for more information.

Examples

CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- Containment
SELECT int4range(10, 20) @> 3;

-- Overlaps
SELECT numrange(11.1, 22.2) && numrange(20.0, 30.0);

-- Extract the upper bound
SELECT upper(int8range(15, 25));

-- Compute the intersection
SELECT int4range(10, 20) * int4range(15, 25);

-- Is the range empty?
SELECT isempty(numrange(1, 5));

Inclusive and exclusive bounds

Every non-empty range has two bounds: the lower bound and the upper bound. All points between these values are included in the range. An inclusive bound means that the boundary point is included in the range, and an exclusive bound means that the boundary point isn't included in the range.

In the text form of a range, an inclusive lower bound is represented by [ while an exclusive lower bound is represented by (. Likewise, an inclusive upper bound is represented by ], while an exclusive upper bound is represented by ).

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

Infinite (unbounded) ranges

You can omit the lower bound of a range, which means that all points less than the upper bound are included in the range. Likewise, if you omit the upper bound of the range, then all points greater than the lower bound are included in the range. If you omit the lower and upper bounds, all values of the element type are considered to be in the range.

Omitting the lower or upper bound is equivalent to considering that the lower bound is minus infinity or the upper bound is plus infinity, respectively. But these infinite values are never values of the range's element type and can never be part of the range. So there's no such thing as an inclusive infinite bound. If you try to write one, it's converted to an exclusive bound.

Also, some element types have a notion of infinity, but that's just another value as far as the range type mechanisms are concerned. For example, in timestamp ranges, [today,] means the same thing as [today,). But [today,infinity] means something different from [today,infinity). The latter excludes the special timestamp value infinity.

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

Range input/output

The input for a range value must follow one of the following patterns:

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

The parentheses or brackets indicate whether the lower and upper bounds are exclusive or inclusive, as described previously. The final pattern is empty, which represents an empty range, that is, a range that contains no points.

The lower bound can be either a string that's valid input for the subtype or empty to indicate no lower bound. Likewise, the upper bound can be either a string that's valid input for the subtype or empty to indicate no upper bound.

You can quote each bound value using double quotes ("). The double quotes are necessary if the bound value contains parentheses, brackets, commas, double quotes, or backslashes. Without the quotes, these characters are otherwise taken as part of the range syntax. To put a double quote or backslash in a quoted bound value, precede it with a backslash. Also, a pair of double quotes within a double-quoted bound value is taken to represent a double-quote character, analogous to the rules for single quotes in SQL literal strings. Alternatively, you can avoid quoting and use backslash escaping to protect all data characters that would otherwise be taken as range syntax. Also, to write a bound value that's an empty string, write "", since writing nothing means an infinite bound.

Whitespace is allowed before and after the range value, but any whitespace between the parentheses or brackets is taken as part of the lower- or upper-bound value. Whether it's significant depends on the element type.

Examples:

-- includes 3, does not include 7, and does include all points in between
SELECT '[3,7)'::int4range;

-- does not include either 3 or 7, but includes all points in between
SELECT '(3,7)'::int4range;

-- includes only the single point 4
SELECT '[4,4]'::int4range;

-- includes no points (and will be normalized to 'empty')
SELECT '[4,4)'::int4range;

Constructing ranges

Each range type has a constructor function with the same name as the range type. Using the constructor function is frequently more convenient than writing a range literal constant, since it avoids the need for extra quoting of the bound values. The constructor function accepts two or three arguments. The two-argument form constructs a range in standard form (lower-bound inclusive, upper-bound exclusive). The three-argument form constructs a range with bounds of the form specified by the third argument. The third argument must be one of the strings "()", "(]", "[)", or "[]". For example:

-- The full form is: lower bound, upper bound, and text argument indicating
-- inclusivity/exclusivity of bounds.
SELECT numrange(1.0, 14.0, '(]');

-- If the third argument is omitted, '[)' is assumed.
SELECT numrange(1.0, 14.0);

-- Although '(]' is specified here, on display the value will be converted to
-- canonical form, since int8range is a discrete range type (see below).
SELECT int8range(1, 14, '(]');

-- Using NULL for either bound causes the range to be unbounded on that side.
SELECT numrange(NULL, 2.2);

Discrete range types

A discrete range is one whose element type has a well-defined step, such as integer or date. In these types, two elements can be said to be adjacent when there are no valid values between them. This contrasts with continuous ranges, where it's always (or almost always) possible to identify other element values between two given values. For example, a range over the numeric type is continuous, as is a range over timestamp. Even though timestamp has limited precision and can theoretically be treated as discrete, it's better to consider it continuous since the step size is normally not of interest.

Another way to think about a discrete range type is that there's a clear idea of a next or previous value for each element value. Knowing that, it's possible to convert between inclusive and exclusive representations of a range's bounds by choosing the next or previous element value instead of the one originally given. For example, in an integer range type, [4,8] and (3,9) denote the same set of values, but this isn't true for a range over numeric.

A discrete range type must have a canonicalization function that's aware of the desired step size for the element type. The canonicalization function is charged with converting equivalent values of the range type to have identical representations, in particular consistently inclusive or exclusive bounds. If a canonicalization function isn't specified, then ranges with different formatting are always treated as unequal, even though they might represent the same set of values in reality.

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

Defining new range types

You can define your own range types. The most common reason to do this is to use ranges over subtypes not provided among the built-in range types. For example, to define a new range type of subtype float8:

CREATE TYPE floatrange AS RANGE (
    subtype = float8,
    subtype_diff = float8mi
);

SELECT '[1.234, 5.678]'::floatrange;

Because float8 has no meaningful step, the example doesn't define a canonicalization function.

If the subtype is considered to have discrete rather than continuous values, the CREATE TYPE command must specify a canonical function. The canonicalization function takes an input range value and must return an equivalent range value that might have different bounds and formatting. The canonical output for two ranges that represent the same set of values, for example, the integer ranges [1, 7] and [1, 8), must be identical. It doesn't matter which representation you choose to be the canonical one, as long as two equivalent values with different formatting are always mapped to the same value with the same formatting. In addition to adjusting the inclusive/exclusive bounds format, a canonicalization function might round off boundary values, in case the desired step size is larger than what the subtype is capable of storing. For instance, you can define a range type over timestamp to have a step size of an hour. In that case, the canonicalization function needs to round off bounds that weren't a multiple of an hour, or perhaps throw an error instead.

Defining your own range type also allows you to specify a different subtype B-tree operator class or collation to change the sort ordering that determines which values fall into a given range.

In addition, any range type that's meant to be used with GiST or SP-GiST indexes must define a subtype difference, or subtype_diff, function. The index still works without subtype_diff, but it's likely to be considerably less efficient than if a difference function is provided. The subtype difference function takes two input values of the subtype and returns their difference, for example, X minus Y, represented as a float8 value. In the earlier example, the function that underlies the regular float8 minus operator can be used. But for any other subtype, some type conversions are necessary. Some creative thought about how to represent differences as numbers might be needed, too. To the greatest extent possible, the subtype_diff function must agree with the sort ordering implied by the selected operator class and collation. That is, its result must be positive whenever its first argument is greater than its second according to the sort ordering.

See the PostgreSQL documentation for more information about creating range types.

Indexing

You can create GiST and SP-GiST indexes for table columns of range types. For instance, to create a GiST index:

CREATE INDEX reservation_idx ON reservation USING gist (during);

A GiST or SP-GiST index can accelerate queries involving these range operators: =, &&, <@, @>, <<, >>, -|-, &<, and &>.

In addition, you can create B-tree and hash indexes for table columns of range types. For these index types, basically the only useful range operation is equality. There's a B-tree sort ordering defined for range values, with corresponding < and > operators, but the ordering is rather arbitrary and not usually useful in the real world. B-tree and hash support for range types is primarily meant to allow sorting and hashing internally in queries rather than for creating actual indexes.

Constraints on ranges

While UNIQUE is a natural constraint for scalar values, it's usually unsuitable for range types. Instead, an exclusion constraint is often more appropriate. Exclusion constraints allow for specifying constraints, such as non-overlapping on a range type. For example:

CREATE TABLE reservation (
    during tsrange,
    EXCLUDE USING gist (during WITH &&)
);

That constraint prevents any overlapping values from existing in the table at the same time:

INSERT INTO reservation VALUES
    ('[2010-01-01 11:30, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO reservation VALUES
    ('[2010-01-01 14:45, 2010-01-01 15:45)');
ERROR:  conflicting key value violates exclusion constraint "reservation_during_excl"
DETAIL:  Key (during)=(["2010-01-01 14:45:00","2010-01-01 15:45:00")) conflicts
with existing key (during)=(["2010-01-01 11:30:00","2010-01-01 15:00:00")).

You can use the btree_gist extension to define exclusion constraints on plain scalar data types, which can then be combined with range exclusions for maximum flexibility. For example, with btree_gist installed, the following constraint rejects overlapping ranges only if the meeting room numbers are equal:

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING gist (room WITH =, during WITH &&)
);

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:00, 2010-01-01 15:00)');
INSERT 0 1

INSERT INTO room_reservation VALUES
    ('123A', '[2010-01-01 14:30, 2010-01-01 15:30)');
ERROR:  conflicting key value violates exclusion constraint "room_reservation_room_during_excl"
DETAIL:  Key (room, during)=(123A, ["2010-01-01 14:30:00","2010-01-01 15:30:00")) conflicts
with existing key (room, during)=(123A, ["2010-01-01 14:00:00","2010-01-01 15:00:00")).

INSERT INTO room_reservation VALUES
    ('123B', '[2010-01-01 14:30, 2010-01-01 15:30)');
INSERT 0 1

• On this page
• Overview
• Built-in range types
• Examples
• Inclusive and exclusive bounds
• Infinite (unbounded) ranges
• Range input/output
• Constructing ranges
• Discrete range types
• Defining new range types
• Indexing
• Constraints on ranges