Database Reference
In-Depth Information
except that if everyone agrees to using that representation for discrete ranges, we can
easily compare among many ranges without having to worry first about converting open
to close or vice versa. PostgreSQL canonicalizes all discrete ranges, for both storage and
display. So if you enter a date range as
(2014-1-5,2014-2-1]
, PostgreSQL rewrites it
as
[2014-01-06,2014-02-02)
.
Built-in Range Types
PostgreSQL comes with six built-in range types for numbers and datetimes:
int4range
,
int8range
A range of integers. Integer ranges are discrete and subject to canonicalization.
numrange
A continuous range of decimals, floating-point numbers, or double-precision num‐
bers.
daterange
A discrete date range of calendar dates without time zone awareness.
tsrange
,
tstzrange
A continuous date and time (timestamp) range allowing for fractional seconds.
tstrange
is not time-zone-aware.
tstzrange
is time-zone-aware.
For number-like ranges, if either the start point or the end point is left blank, PostgreSQL
replaces it with a
null
. For practicality, you can interpret the null to represent either
-infinity
on the left or
infinity
on the right. In actuality, you're bound by the smallest
and largest values for the particular data type. So a
int4range
of
(,)
would be
[-2147483648,2147483647)
.
For temporal ranges,
-infinity
and
infinity
are valid upper and lower bounds.
In addition to the built-in range types, you can create your own range types. When you
do, you can set the range to be either discrete or continuous.
Defining Ranges
A range, regardless of type, is always composed of two elements of the same type with
bounding condition denoted by
[
,
]
,
(
, or
)
, as shown in
Example 5-19
.
Example 5-19. Defining ranges with casts
SELECT
'[2013-01-05,2013-08-13]'
::
daterange
;
SELECT
'(2013-01-05,2013-08-13]'
::
daterange
;
SELECT
'(0,)'
::
int8range
;
SELECT
'(2013-01-05 10:00,2013-08-13 14:00]'
::
tsrange
;
[2013-01-05,2013-08-14)
[2013-01-06,2013-08-14)
