8.17. Range Types
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 welldefined 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.
8.17.1. Builtin Range Types
PostgreSQL comes with the following builtin range types:

int4range
 Range ofinteger

int8range
 Range ofbigint

numrange
 Range ofnumeric

tsrange
 Range oftimestamp without time zone

tstzrange
 Range oftimestamp with time zone

daterange
 Range ofdate
In addition, you can define your own range types; see CREATE TYPE for more information.
8.17.2. Examples
CREATE TABLE reservation (room int, during tsrange); INSERT INTO reservation VALUES (1108, '[20100101 14:30, 20100101 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.
8.17.3. Inclusive and Exclusive Bounds
Every nonempty 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.
8.17.4. Infinite (Unbounded) Ranges
The lower bound of a range can be omitted, meaning that all
values less than the upper bound are included in the range, e.g.,
(,3]
. Likewise, if the upper bound of the range
is omitted, then all values 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. Specifying a
missing bound as inclusive is automatically converted to exclusive,
e.g.,
[,]
is converted to
(,)
.
You can think of these missing values as +/infinity, but they are
special range type values and are considered to be beyond any range
element type's +/infinity values.
Element types that have the notion of
"
infinity
"
can
use them as explicit bound values. For example, with timestamp
ranges,
[today,infinity)
excludes the special
timestamp
value
infinity
,
while
[today,infinity]
include it, as does
[today,)
and
[today,]
.
The functions
lower_inf
and
upper_inf
test for infinite lower
and upper bounds of a range, respectively.
8.17.5. Range Input/Output
The input for a range value must follow one of the following patterns:
(lowerbound
,upperbound
) (lowerbound
,upperbound
] [lowerbound
,upperbound
) [lowerbound
,upperbound
] 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
lowerbound
may be either a string
that is valid input for the subtype, or empty to indicate no
lower bound. Likewise,
upperbound
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 doublequoted 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 backslashescaping 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.)
Note
These rules are very similar to those for writing field values in compositetype 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;
8.17.6. 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 twoargument form constructs a range
in standard form (lower bound inclusive, upper bound exclusive), while
the threeargument 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);
8.17.7. Discrete Range Types
A discrete range is one whose element type has a welldefined
"
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 builtin range types
int4range
,
int8range
,
and
daterange
all use a canonical form that includes
the lower bound and excludes the upper bound; that is,
[)
. Userdefined range types can use other conventions,
however.
8.17.8. Defining New Range Types
Users can define their own range types. The most common reason to do
this is to use ranges over subtypes not provided among the builtin
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 Btree 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 SPGiST
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 lessoversimplified 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.
8.17.9. Indexing
GiST and SPGiST 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 SPGiST index can accelerate queries involving these range operators:
=
,
&&
,
<@
,
@>
,
<<
,
>>
,

,
&<
, and
&>
(see
Table 9.50
for more information).
In addition, Btree 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 Btree 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' Btree and hash support is primarily meant to
allow sorting and hashing internally in queries, rather than creation of
actual indexes.
8.17.10. Constraints on Ranges
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
"
nonoverlapping
"
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 ('[20100101 11:30, 20100101 15:00)'); INSERT 0 1 INSERT INTO reservation VALUES ('[20100101 14:45, 20100101 15:45)'); ERROR: conflicting key value violates exclusion constraint "reservation_during_excl" DETAIL: Key (during)=(["20100101 14:45:00","20100101 15:45:00")) conflicts with existing key (during)=(["20100101 11:30:00","20100101 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', '[20100101 14:00, 20100101 15:00)'); INSERT 0 1 INSERT INTO room_reservation VALUES ('123A', '[20100101 14:30, 20100101 15:30)'); ERROR: conflicting key value violates exclusion constraint "room_reservation_room_during_excl" DETAIL: Key (room, during)=(123A, ["20100101 14:30:00","20100101 15:30:00")) conflicts with existing key (room, during)=(123A, ["20100101 14:00:00","20100101 15:00:00")). INSERT INTO room_reservation VALUES ('123B', '[20100101 14:30, 20100101 15:30)'); INSERT 0 1