RANGE TYPESData 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

Show:
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 ✓ Supported 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: Since 9.2 SQL Server: Not supported Oracle: Not supported SQLite: Not supported

Per-Database Syntax & Notes

MySQL

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

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

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

-- Not supported.

Oracle

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

-- Not supported.

SQLite

No range types. Use two columns.

-- Not supported.