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 is 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; but price ranges, measurement ranges from an instrument, and so forth can also be useful.
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 CREATE TYPE for more information.
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));
See Table 9.50 and Table 9.51 for complete lists of operators and functions on range types.
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 itself is included in the range as well, while an exclusive bound means that the boundary point is not 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 “)
”. (See Section 8.17.5 for
more details.)
The functions lower_inc
and
upper_inc
test the inclusivity of
the lower and upper bounds of a range value, respectively.
The lower bound of a range can be omitted, meaning that all points less than the upper bound are included in the range. Likewise, if the upper bound of the range is omitted, then all points greater than the lower bound are included in the range. If both lower and upper bounds are omitted, all values of the element type are considered to be in the range.
This is equivalent to considering that the lower bound is “minus infinity”, or the upper bound is “plus infinity”, respectively. But note that these infinite values are never values of the range's element type, and can never be part of the range. (So there is no such thing as an inclusive infinite bound — if you try to write one, it will automatically be converted to an exclusive bound.)
Also, some element types have a notion of “infinity”, but that
is just another value so 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.
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. Notice that the final pattern is empty
, which represents an empty range (a
range that contains no points).
The lower-bound
may be either a string that is valid input for the subtype, or
empty to indicate no lower bound. Likewise, upper-bound
may be either a
string that is valid input for the subtype, or empty to
indicate no upper bound.
Each bound value can be quoted using "
(double quote) characters. This is necessary
if the bound value contains parentheses, brackets, commas,
double quotes, or backslashes, since these characters would
otherwise be 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, analogously 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 is 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. (Depending on the element type, it might or might not be significant.)
These rules are very similar to those for writing field values in composite-type literals. See Section 8.16.6 for additional commentary.
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;
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), while 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);
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 so could
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 is a clear idea of a “next” or “previous” value for
each element value. Knowing that, it is 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
would not be so for a range over numeric.
A discrete range type should have a canonicalization function that is 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 is not specified, then ranges with different formatting will always be 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.
Users can define their 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”,
we do not define a canonicalization function in this
example.
Defining your own range type also allows you to specify a different subtype B-tree operator class or collation to use, so as to change the sort ordering that determines which values fall into a given range.
If the subtype is considered to have discrete rather than
continuous values, the CREATE TYPE
command should specify a canonical
function. The canonicalization function takes an input range
value, and must return an equivalent range value that may 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, so long as two equivalent values with different
formattings 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, a range
type over timestamp
could be defined
to have a step size of an hour, in which case the
canonicalization function would need to round off bounds that
weren't a multiple of an hour, or perhaps throw an error
instead.
In addition, any range type that is meant to be used with
GiST or SP-GiST indexes should define a subtype difference, or
subtype_diff
, function. (The index
will still work without subtype_diff
, but it is 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 (i.e.,
X
minus Y
) represented as a
float8
value. In our example above,
the function float8mi
that
underlies the regular float8
minus
operator can be used; but for any other subtype, some type
conversion would be necessary. Some creative thought about how
to represent differences as numbers might be needed, too. To
the greatest extent possible, the subtype_diff
function should agree with the
sort ordering implied by the selected operator class and
collation; that is, its result should be positive whenever its
first argument is greater than its second according to the sort
ordering.
A less-oversimplified example of a subtype_diff
function is:
CREATE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS 'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE; CREATE TYPE timerange AS RANGE ( subtype = time, subtype_diff = time_subtype_diff ); SELECT '[11:10, 23:00]'::timerange;
See CREATE TYPE for more information about creating range types.
GiST and SP-GiST indexes can be created 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 &>
(see Table 9.50 for
more information).
In addition, B-tree and hash indexes can be created for
table columns of range types. For these index types, basically
the only useful range operation is equality. There is 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. Range
types' B-tree and hash support is primarily meant to allow
sorting and hashing internally in queries, rather than creation
of actual indexes.
While UNIQUE
is a natural
constraint for scalar values, it is usually unsuitable for
range types. Instead, an exclusion constraint is often more
appropriate (see CREATE TABLE ...
CONSTRAINT ... EXCLUDE). Exclusion constraints allow the
specification of constraints such as “non-overlapping” on
a range type. For example:
CREATE TABLE reservation ( during tsrange, EXCLUDE USING GIST (during WITH &&) );
That constraint will prevent 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, after btree_gist
is
installed, the following constraint will reject 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
If you see anything in the documentation that is not correct, does not match your experience with the particular feature or requires further clarification, please use this form to report a documentation issue.