PostgreSQL Range Types

Introduction

Range types are a powerful feature in PostgreSQL that allow you to store and manipulate ranges of values within a single column. Instead of using two separate columns to represent the bounds of a range (like a start date and an end date), PostgreSQL's range types encapsulate this concept into a single data type with built-in validation and specialized operators.

Range types are particularly useful when dealing with:

• Time periods (date/time ranges)
• Numeric ranges (like price ranges)
• IP address ranges
• And many other scenarios where you need to work with intervals

This tutorial will introduce you to PostgreSQL range types, explain how they work, and demonstrate practical examples of their use.

Available Range Types

PostgreSQL provides several built-in range types:

Range Type	Description	Element Type
int4range	Range of integers	integer
int8range	Range of bigints	bigint
numrange	Range of numerics	numeric
tsrange	Range of timestamps without time zone	timestamp
tstzrange	Range of timestamps with time zone	timestamp with time zone
daterange	Range of dates	date

Range Syntax

Ranges in PostgreSQL use the following syntax:

sql

'[lower_bound, upper_bound]'

Where:

• Square brackets [ ] denote an inclusive bound
• Parentheses ( ) denote an exclusive bound
• The lower and upper bounds are separated by a comma

Examples of range literals:

sql

-- Inclusive lower bound, exclusive upper bound (most common)
'[2022-01-01, 2022-12-31)'

-- Both bounds inclusive
'[1, 10]'

-- Both bounds exclusive
'(0, 5)'

-- Unbounded ranges (infinity)
'[2022-01-01,)' -- From Jan 1, 2022 onwards (unbounded above)
'(,100]' -- Up to and including 100 (unbounded below)

Creating Tables with Range Types

Let's create a table that uses range types:

sql

CREATE TABLE hotel_bookings (
    booking_id SERIAL PRIMARY KEY,
    room_number INTEGER,
    guest_name TEXT,
    stay_period daterange
);

Inserting Data with Range Types

Now, let's insert some bookings:

sql

INSERT INTO hotel_bookings (room_number, guest_name, stay_period)
VALUES 
    (101, 'Alice Smith', '[2023-06-01, 2023-06-05)'),
    (102, 'Bob Johnson', '[2023-06-03, 2023-06-10)'),
    (101, 'Charlie Brown', '[2023-06-10, 2023-06-15)');

Querying Range Types

Checking if a value is contained in a range

sql

-- Find bookings that include June 4, 2023
SELECT booking_id, room_number, guest_name, stay_period
FROM hotel_bookings
WHERE stay_period @> DATE '2023-06-04';

Result:

 booking_id | room_number | guest_name  |      stay_period      
------------+-------------+-------------+-----------------------
          1 | 101         | Alice Smith | [2023-06-01,2023-06-05)
          2 | 102         | Bob Johnson | [2023-06-03,2023-06-10)

Checking for overlapping ranges

sql

-- Find all bookings that overlap with a specific period
SELECT booking_id, room_number, guest_name, stay_period
FROM hotel_bookings
WHERE stay_period && daterange('2023-06-04', '2023-06-12');

Result:

 booking_id | room_number | guest_name     |      stay_period      
------------+-------------+----------------+-----------------------
          1 | 101         | Alice Smith    | [2023-06-01,2023-06-05)
          2 | 102         | Bob Johnson    | [2023-06-03,2023-06-10)
          3 | 101         | Charlie Brown  | [2023-06-10,2023-06-15)

Finding conflicts (overlapping bookings for the same room)

sql

SELECT a.booking_id, a.guest_name AS guest_1, 
       b.booking_id, b.guest_name AS guest_2,
       a.stay_period, b.stay_period
FROM hotel_bookings a, hotel_bookings b
WHERE a.booking_id < b.booking_id
  AND a.room_number = b.room_number
  AND a.stay_period && b.stay_period;

Range Operators

PostgreSQL provides many operators for working with range types:

Operator	Description	Example
=	Equal	daterange('2023-01-01', '2023-01-31') = daterange('2023-01-01', '2023-01-31')
<>	Not equal	int4range(1, 10) <> int4range(1, 11)
<	Less than	int4range(1, 10) < int4range(11, 20)
>	Greater than	int4range(11, 20) > int4range(1, 10)
<=	Less than or equal	int4range(1, 10) <= int4range(1, 10)
>=	Greater than or equal	int4range(1, 10) >= int4range(1, 10)
@>	Contains	int4range(1, 10) @> 5
<@	Contained by	5 <@ int4range(1, 10)
&&	Overlaps	int4range(1, 5) && int4range(3, 7)
<<	Strictly left of	int4range(1, 5) << int4range(10, 20)
>>	Strictly right of	int4range(10, 20) >> int4range(1, 5)
`-	-`	Adjacent to
+	Union	int4range(1, 5) + int4range(5, 10)
*	Intersection	int4range(1, 7) * int4range(5, 10)
-	Difference	int4range(1, 10) - int4range(5, 7)

Range Functions

In addition to operators, PostgreSQL provides several functions for working with ranges:

sql

-- Create a range
SELECT daterange('2023-01-01', '2023-01-31');

-- Get the lower bound
SELECT lower(daterange('2023-01-01', '2023-01-31'));

-- Get the upper bound
SELECT upper(daterange('2023-01-01', '2023-01-31'));

-- Check if a range is empty
SELECT isempty(daterange('2023-01-01', '2023-01-01'));

-- Get the lower bound with inclusion flag
SELECT lower_inc(daterange('2023-01-01', '2023-01-31'));

-- Get the upper bound with inclusion flag
SELECT upper_inc(daterange('2023-01-01', '2023-01-31'));

-- Get the lower bound with infinity flag
SELECT lower_inf(daterange('2023-01-01', '2023-01-31'));

-- Get the upper bound with infinity flag
SELECT upper_inf(daterange('2023-01-01', '2023-01-31'));

Real-world Applications

Example 1: Event Scheduling System

sql

-- Create an events table
CREATE TABLE events (
    event_id SERIAL PRIMARY KEY,
    event_name TEXT,
    venue_id INTEGER,
    event_period tsrange
);

-- Insert some events
INSERT INTO events (event_name, venue_id, event_period)
VALUES 
    ('Conference', 1, '[2023-07-10 09:00:00, 2023-07-10 17:00:00)'),
    ('Workshop', 1, '[2023-07-10 13:00:00, 2023-07-10 15:00:00)'),
    ('Concert', 2, '[2023-07-10 19:00:00, 2023-07-10 22:00:00)');

-- Find overlapping events at the same venue
SELECT a.event_name AS event_1, 
       b.event_name AS event_2,
       a.event_period,
       b.event_period
FROM events a, events b
WHERE a.event_id < b.event_id
  AND a.venue_id = b.venue_id
  AND a.event_period && b.event_period;

Example 2: IP Range Management

sql

-- First, enable the btree_gist extension if not already enabled
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Create an IP allocation table
CREATE TABLE ip_allocations (
    allocation_id SERIAL PRIMARY KEY,
    department TEXT,
    ip_range int4range,
    EXCLUDE USING gist (ip_range WITH &&)
);

-- Insert some IP allocations
INSERT INTO ip_allocations (department, ip_range)
VALUES 
    ('Engineering', '[192168001, 192168050]'),
    ('Marketing', '[192168051, 192168100]'),
    ('Finance', '[192168101, 192168150]');

-- Try to insert an overlapping range (this will fail)
INSERT INTO ip_allocations (department, ip_range)
VALUES ('HR', '[192168040, 192168060]');

Notice the EXCLUDE USING gist constraint. This creates an exclusion constraint that prevents overlapping ranges from being inserted, ensuring that IP ranges don't conflict.

Example 3: Price Tiers

sql

CREATE TABLE product_price_tiers (
    tier_id SERIAL PRIMARY KEY,
    product_id INTEGER,
    quantity_range int4range,
    price_per_unit NUMERIC(10,2)
);

-- Insert price tiers (bulk discounts)
INSERT INTO product_price_tiers (product_id, quantity_range, price_per_unit)
VALUES 
    (1, '[1, 10)', 100.00),
    (1, '[10, 50)', 90.00),
    (1, '[50, 100)', 80.00),
    (1, '[100,)', 70.00);

-- Query to find the price for a specific quantity
SELECT tier_id, price_per_unit
FROM product_price_tiers
WHERE product_id = 1 AND quantity_range @> 25;

Creating Custom Range Types

You can create your own custom range types for specific domains:

sql

-- Create a new type for the base element
CREATE TYPE float_range AS RANGE (
    subtype = float8,
    subtype_diff = float8mi
);

-- Now you can use it in tables
CREATE TABLE temperature_readings (
    reading_id SERIAL PRIMARY KEY,
    location TEXT,
    date DATE,
    temperature_range float_range
);

Range Type Indexing

For efficient querying of range types, PostgreSQL supports GiST (Generalized Search Tree) and SP-GiST (Space-Partitioned Generalized Search Tree) indexes:

sql

-- Create a GiST index on the stay_period column
CREATE INDEX idx_hotel_bookings_stay_period
ON hotel_bookings USING GIST (stay_period);

-- Now range queries will be much faster
SELECT * FROM hotel_bookings
WHERE stay_period && daterange('2023-06-01', '2023-06-15');

Summary

PostgreSQL range types provide a powerful way to work with ranges of values in your database. Key benefits include:

1. Data Integrity: Range constraints ensure valid ranges and can prevent invalid data
2. Simplified Queries: Complex range operations are built directly into PostgreSQL
3. Performance: Range types can use specialized indexes for efficient querying
4. Expressiveness: Range types make your database schema more readable and maintainable

Range types are particularly valuable when dealing with time periods, numerical ranges, or any other situation where you need to represent a span of values.

Exercises

1. Create a table to track employee vacation schedules using the daterange type.
2. Write a query to find all employees who will be on vacation on a specific date.
3. Create a constraint to prevent employees in the same department from having overlapping vacation periods.
4. Implement a system for tracking product version compatibility using range types.
5. Create a custom range type for representing geographic coordinates.

Additional Resources

• PostgreSQL Documentation: Range Types
• PostgreSQL Wiki: Range Types
• Using PostgreSQL Range Types With Rails