Range Types
advancedpostgresql
2 min read
What are Range Types?
PostgreSQL range types represent a range of values of a given element type. Instead of storing start and end values in separate columns, you store the entire range in a single column.
Built-in range types include: int4range, int8range, numrange, daterange, tsrange, tstzrange.
Syntax
-- Create a table with a date range
CREATE TABLE events (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
event_period daterange
);
-- Insert a range
INSERT INTO events (name, event_period)
VALUES ('Conference', '[2025-06-01, 2025-06-05]');
-- Query overlapping ranges
SELECT * FROM events
WHERE event_period && '[2025-06-03, 2025-06-10]';When to Use
- Scheduling and booking systems (date/time ranges)
- IP address ranges, version ranges
- Any data with a start and end that represents a continuous set
Key Points
- Inclusive/Exclusive —
[a, b]includes both endpoints.[a, b)excludes the upper bound (default for discrete types). - Operators —
&&(overlaps),@>(contains),<@(contained by),-|-(adjacent). - GiST Index — Index ranges with GiST for fast overlap/containment queries.
- Exclusion Constraint — Prevent overlapping ranges: EXCLUDE USING gist (room WITH =, period WITH &&).
- Empty Ranges — 'empty' represents a range with no values.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
Range Types Challenge
Write a query that find events that overlap with a given date range.
Expected result
Events whose period overlaps with June 3–10, 2025.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are Range Types?
Initializing database...Each run starts from fresh sample data.
More Examples
Check if a date is within a range
Find events happening on a specific date.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What does [) mean in a range?
[ means inclusive (includes the value), ) means exclusive (excludes the value). [1,5) includes 1,2,3,4 but not 5.
Can I create custom range types?
Yes. Use CREATE TYPE my_range AS RANGE (subtype = my_type); to define custom ranges over any orderable type.