Range types (int4range, daterange) in PostgreSQL

Introduction

Range types let you store a range of values in one column, like a span of numbers or dates. This helps keep data organized and easy to check if something falls inside that range.

You want to store a period of dates for a booking or event.

You need to keep track of a range of numbers, like age groups or score intervals.

You want to check if a value falls inside a certain range quickly.

You want to avoid storing start and end values separately and keep ranges as one unit.

You want to find overlapping or adjacent ranges in your data.

Syntax

PostgreSQL

CREATE TABLE table_name (
  column_name int4range,
  another_column daterange
);

int4range stores ranges of integers.

daterange stores ranges of dates.

Examples

This creates an integer range from 1 up to but not including 5.

PostgreSQL

SELECT int4range(1, 5);

This creates a date range from January 1, 2024, up to but not including January 10, 2024.

PostgreSQL

SELECT daterange('2024-01-01', '2024-01-10');

This finds bookings where the date January 5, 2024, is inside the booking date range.

PostgreSQL

SELECT * FROM bookings WHERE booking_dates @> '2024-01-05'::date;

Sample Program

This example creates an events table with a date range column. It inserts three events with their date ranges. Then it finds events happening on June 4, 2024.

PostgreSQL

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  event_name TEXT,
  event_period daterange
);

INSERT INTO events (event_name, event_period) VALUES
('Conference', daterange('2024-06-01', '2024-06-05')),
('Workshop', daterange('2024-06-04', '2024-06-06')),
('Holiday', daterange('2024-06-10', '2024-06-15'));

-- Find events happening on June 4, 2024
SELECT event_name FROM events WHERE event_period @> '2024-06-04'::date;

Important Notes

Ranges are half-open by default: they include the start value but exclude the end value.

You can use operators like @> to check if a range contains a value.

Range types help simplify queries involving intervals and make data easier to manage.

Summary

Range types store intervals of values in one column.

Use int4range for integer ranges and daterange for date ranges.

They make it easy to check if a value is inside a range and to find overlapping ranges.