PolarDB:Range types

Built-in range and multirange types

PolarDB includes the following built-in range types, each paired with a corresponding multirange type:

A multirange stores a set of non-contiguous ranges of the same subtype as a single value. To use ranges over other subtypes, define a custom range type with CREATE TYPE.

-- Create a table with a timestamp range column
CREATE TABLE reservation (room int, during tsrange);
INSERT INTO reservation VALUES
    (1108, '[2010-01-01 14:30, 2010-01-01 15:30)');

-- Containment: does int4range(10,20) contain 3?
SELECT int4range(10, 20) @> 3;

-- Overlap: do these two numranges overlap?
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);

-- Check whether a range is empty
SELECT isempty(numrange(1, 5));

Inclusive and exclusive bounds

Every non-empty range has a lower bound and an upper bound. Points between those bounds are included in the range. Whether the boundary points themselves are included depends on the bound type:

• A *closed* (inclusive) bound includes the boundary point itself.

• An *open* (exclusive) bound excludes the boundary point.

• A *half-open* range mixes the two, including one endpoint but not the other.

In the text form of a range:

The functions lower_inc and upper_inc return true if the lower or upper bound of a range is inclusive.

Unbounded ranges

Omit a bound to make the range extend to infinity in that direction:

• Omit the lower bound: all values less than the upper bound are included — for example, (,3].

• Omit the upper bound: all values greater than the lower bound are included — for example, [3,).

• Omit both bounds: all values of the element type are included.

When an omitted bound is written as inclusive, it is automatically converted to exclusive — [,] becomes (,).

You can think of these missing values as +/- infinity, but they are special range type values considered to be beyond any range element type's +/- infinity values.

For element types that have the notion of "infinity," you can use it as an explicit bound value. For example, with timestamp ranges, [today,infinity) excludes the special timestamp value infinity, while [today,infinity] includes it, as does [today,) and [today,].

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

Range input/output

A range value must follow one of these patterns:

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

empty represents a range that contains no points.

Each bound value is a string valid for the subtype, or blank to indicate an unbounded side. Quote bound values with " (double quotes) if they contain parentheses, brackets, commas, double quotes, or backslashes. To include a literal double quote or backslash in a quoted bound, escape it with a backslash. To write an empty-string bound value, use "" — writing nothing means unbounded.

Whitespace before and after the outer brackets is ignored. Whitespace between the brackets is taken as part of the bound value.

These rules are similar to field values in composite-type literals.

-- Includes 3, excludes 7, and includes all points in between
SELECT '[3,7)'::int4range;

-- Excludes both 3 and 7, includes all points in between
SELECT '(3,7)'::int4range;

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

-- Empty range (normalized to 'empty')
SELECT '[4,4)'::int4range;

Constructing ranges and multiranges

Each range type has a constructor function with the same name. Use the constructor instead of a literal string when your bound values contain characters that would need escaping.

• Two-argument form: constructs a range with inclusive lower bound and exclusive upper bound — equivalent to [).

• Three-argument form: the third argument controls the bound types and must be one of (), (], [), or [].

-- Three-argument form: lower bound exclusive, upper bound inclusive
SELECT numrange(1.0, 14.0, '(]');

-- Two-argument form: defaults to '[)' (inclusive lower, exclusive upper)
SELECT numrange(1.0, 14.0);

-- The stored value is canonicalized — '(]' for integers becomes '[)'
SELECT int8range(1, 14, '(]');

-- NULL for either bound makes that side unbounded
SELECT numrange(NULL, 2.2);

Discrete range types

A discrete range type is one whose subtype has a clear "step" between adjacent values, such as integer or date. Two values are adjacent when no valid values exist between them.

Continuous range types, such as numeric and timestamp, always have values between any two given points.

Because discrete ranges have a canonical form, equivalent representations collapse to the same value. For example, [4,8] and (3,9) denote the same set of integer values. Continuous ranges do not have this property.

The built-in discrete range types (int4range, int8range, and daterange) all use the canonical form [) — inclusive lower bound, exclusive upper bound.

When defining a custom discrete range type, specify a canonical function in CREATE TYPE. The canonicalization function maps equivalent range values to a single representation. Without it, two ranges that represent the same set of values are treated as unequal.

Define new range types

Define a custom range type when you need ranges over a subtype not covered by the built-in types.

-- Define a range type over float8
CREATE TYPE floatrange AS RANGE (
    subtype = float8,
    subtype_diff = float8mi
);

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

Because float8 is continuous, this example omits a canonicalization function.

A custom range type also lets you specify a different B-tree operator class or collation to change the sort ordering for the range.

If the subtype is discrete, specify a canonical function in CREATE TYPE. The function receives an input range and returns an equivalent range with a consistent representation. It can also round boundary values when the desired step size is larger than the subtype's precision.

For GiST or SP-GiST indexes, also define a subtype_diff function. The function takes two subtype values and returns their difference as float8. Without it, the index still works but is less efficient. The difference should be positive when the first argument is greater than the second according to the sort ordering.

-- Define a subtype_diff function for the time type
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;

For full syntax, see CREATE TYPE.

Indexing

GiST and SP-GiST indexes work on range type columns. GiST indexes also work on multirange type columns.

CREATE INDEX reservation_idx ON reservation USING GIST (during);

GiST and SP-GiST indexes accelerate queries that use these operators: =, &&, <@, @>, <<, >>, -|-, &<, and &>.

B-tree and hash indexes are also supported for range columns, but they are primarily useful for internal sorting and hashing in queries rather than for range-specific operations. The only range operation they accelerate effectively is equality (=).

Constraints on ranges

UNIQUE constraints are rarely appropriate for range columns, because two non-identical ranges can overlap significantly. Use an exclusion constraint instead to enforce a "no overlap" rule:

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

This constraint prevents any two rows from having overlapping during values:

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")).

To combine range exclusions with scalar column constraints, install the btree_gist extension. The following example rejects overlapping reservations only when the room numbers match:

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