RANGE TYPES

Data Types
Buy Me A Coffee

First-class data types representing a continuous range of values (integers, dates, timestamps) with built-in operators for containment, overlap, and adjacency.

PostgreSQL types that represent an interval with a lower and upper bound: int4range, daterange, tsrange. Enable efficient storage and querying of temporal intervals. Useful for reservation systems and versioned records.

Compatibility

Filter by Database
SQL RANGE TYPES Compatibility Across Databases
Database System Support Status Since Version Notes
MySQL Not Supported - No range types. Use two columns (start_date, end_date) and enforce constraints manually or via triggers.
PostgreSQL Native 9.2 Built-in types: int4range, int8range, numrange, tsrange, tstzrange, daterange. Multirange types (int4multirange, etc.) added in PG 14. Operators: @> (contains), <@ (contained by), && (overlaps), << (strictly left of), >> (strictly right of), -|- (adjacent). GiST indexable. Boundary inclusivity is explicit: [1,5) vs (1,5].
SQL Server Not Supported - No range types. Temporal tables (2016+) handle system-time history but are not general-purpose range types.
Oracle Not Supported - No range types. INTERVAL types exist but represent durations, not ranges.
SQLite Not Supported - No range types. Use two columns.

Details

Unique to PostgreSQL. Range types are especially powerful in combination with exclusion constraints and GiST indexes — the canonical use case is a double-booking prevention system.

Standard Syntax

-- Does a range contain a point? SELECT '[1,10)'::int4range @> 5; -- true -- Do two ranges overlap? SELECT '[1,5)'::int4range && '[3,8)'::int4range; -- true

Version Support

MySQL: Not supported PostgreSQL: Native since 9.2 SQL Server: Not supported Oracle: Not supported SQLite: Not supported

Per-Database Syntax & Notes

MySQL Alternative syntax

No range types. Use two columns (start_date, end_date) and enforce constraints manually or via triggers.

-- Not supported. SELECT * FROM bookings WHERE start_date < '2024-02-01' AND end_date > '2024-01-01';

PostgreSQL Native syntax

Built-in types: int4range, int8range, numrange, tsrange, tstzrange, daterange. Multirange types (int4multirange, etc.) added in PG 14. Operators: @> (contains), <@ (contained by), && (overlaps), << (strictly left of), >> (strictly right of), -|- (adjacent). GiST indexable. Boundary inclusivity is explicit: [1,5) vs (1,5].

SELECT '[1,5)'::int4range @> 3; -- true SELECT '[1,5)'::int4range && '[3,8)'::int4range; -- true CREATE TABLE events ( name TEXT, during TSRANGE ); CREATE INDEX ON events USING gist (during); -- Query events overlapping a window: SELECT * FROM events WHERE during && '[2024-01-01,2024-02-01)'::tsrange;

SQL Server Alternative syntax

No range types. Temporal tables (2016+) handle system-time history but are not general-purpose range types.

-- Not supported.

Oracle Alternative syntax

No range types. INTERVAL types exist but represent durations, not ranges.

-- Not supported.

SQLite Alternative syntax

No range types. Use two columns.

-- Not supported.