Database Reference
In-Depth Information
can appear only in the
FROM
clause.
Example 5-18
revisits our unbalanced arrays using
the version 9.4 construct.
Example 5-18. Unnesting unbalanced arrays with multiargument unnest
SELECT
*
FROM
unnest
(
'{blind,mouse}'
::
text
[],
'{1,2,3}'
::
int
[])
As
f
(
t
,
i
);
t | i
-------+---
blind | 1
mouse | 2
<NULL> | 3
Range Types
Range data types
are data types introduced in version 9.2 that define a range of values.
Besides adding the convenience of having to deal with one fewer field, PostgreSQL also
rolled out many operators and functions to identify overlapping ranges, check to see if
a value falls inside the range, and combine adjacent smaller ranges into larger ranges.
Prior to range types, we had to kludge our own functions. These often were clumsy and
slow, and didn't always produce the expected results. We've been so happy with ranges
that we've converted all of our temporal tables to use them where possible. We hope you
share our joy.
Range types replace the need to use two separate fields to represent ranges. Suppose we
want all integers between -2 and 2, but not including 2. The range representation would
be
[-2,2)
. The square bracket indicates a range that is closed on that end, whereas a
parenthesis indicates a range that is open on that end. Thus,
[-2,2)
includes exactly
four integers: -2, -1, 0, 1. Similarly:
• The range
(-2,2]
would have four integers:
-1, 0, 1, 2
.
• The range
(-2,2)
would have three integers:
-1, 0, 1
.
• The range
[-2,2]
would have five integers:
-2, -1, 0, 1, 2
.
Discrete Versus Continuous Ranges
PostgreSQL makes a distinction between discrete and continuous ranges. A range of
integers or dates is discrete because you can enumerate each value within the range.
Think of dots on a number line. A range of numerics or timestamps is continuous,
because an infinite number of values lie between the end points.
A discrete range has multiple representations. Our earlier example of
[-2,2)
can be
represented in the following ways and still include the same number of values in the
range:
[-2,1], (-3,1], (-3,2), [-2,2)
. Of these four representations, the one with
[)
is considered the
canonical form
. There's nothing magical about closed-open ranges
