âš¡IntermediateJSON & JSONB
Range Types
Store and query continuous ranges of values using PostgreSQL's built-in range types.
Overview
Range types represent a continuous span of values date ranges, number ranges, IP address ranges. PostgreSQL includes: int4range, int8range, numrange, tsrange, tstzrange, daterange, and you can create custom range types. Range operators check containment (@>), overlap (&&), and adjacency (-|-).
The EXCLUDE USING GIST constraint prevents overlapping ranges in the same column perfect for booking systems, scheduling, and resource allocation.
Key Points
- •daterange, tstzrange, int4range, numrange built-in range types
- •[lower,upper] inclusive; (lower,upper) exclusive; empty empty range
- •@> containment: range @> value or range @> range
- •&& overlap: two ranges overlap
- •-|- adjacency: ranges are adjacent (no gap)
- •EXCLUDE USING GIST (col WITH &&) no overlapping ranges constraint
Examples
Range type usage
-- Table with date ranges (e.g., hotel bookings)
CREATE TABLE bookings (
id SERIAL PRIMARY KEY,
room_id INT NOT NULL,
period daterange NOT NULL,
EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);
-- Insert booking
INSERT INTO bookings (room_id, period) VALUES (1, '[2024-06-01, 2024-06-07)');
-- Check overlap
SELECT * FROM bookings WHERE period && '[2024-06-05, 2024-06-10)';
-- Is date in range?
SELECT * FROM bookings WHERE period @> '2024-06-03'::date;Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4