PostgreSQL Range types

See also

Introduction

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.

Built-in Range Types

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