PostgreSQL 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 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