Postgres Ranges

Postgres supports multiple built-in range datatypes:

• int4range
• int8range
• numrange
• tsrange (range with timestamp without timezone)
• tstzrange (range with timestamp with timezone)
• daterange

They represent a start and endpoint of something in a single column. Image you're building a vacation booking feature:

Copycreate_table :events do |t|
  t.date :starts_on
  t.date :ends_on
end

This is how you would use a range type in a migration:

Copycreate_table :vacations do |t|
  t.daterange :period
end

See the docs Show archive.org snapshot for more details.

Features

ActiveRecord Support

ActiveRecord supports ranges and knows how to properly cast them:

Copyvacation = Vacation.create(period: "[2025-12-12,2026-12-24)")
vacation.period.class  # Range

# ActiveRecord can cast as well
vacation = Vacation.create(period: 2.weeks.ago..1.day.ago)

Note

In Ruby 1...10 excludes 10 and 1..10 includes 10. Ruby also supports 1.. and ..1 as endless ranges.

Syntax

Copy[,]::daterange or (,)::daterange // contains all days
[2025-12-12, 2026-12-12)::daterange // 12.12.2025 until but not including 12.12.2026
[2025-12-12, 2026-12-12]::daterange // 12.12.2025 until 12.12.2026
[2025-12-12, ]::daterange // starting 12.12.2025
(1,10)::int4range // integer range including 2,3,4,5,6,7,8,9

Please be aware there's a difference between an endless range or a range with a null value. You probably don't want to allow null values in your ranges most of the time.

Simple SQL queries

There are a ton of operators Show archive.org snapshot that can be used with ranges. They all deal with endless ranges in a sensible way and can be indexed.

CopyEvent.where("period && daterange(2025-12-24, 2025-12-31)") # all events that have some **overlap** with the end of year 2025
Event.where("upper(period) <= ?::date") # all events where the end of the period is before `?`
Event.where("period -|- daterange('2025-12-12', '2026-12-01')") # all events directly adjacent to 2025-12-12 

Avoid overlaps

You can implement a simple exclusion constraint to avoid overlapping bookings. This avoids overlapping dateranges on the database level, avoiding race conditions etc.:

Copyadd_exclusion_constraint(:events, with: "period WITH &&") ## && is the overlap operator

PG Ranges are ordered, so in the example above the start of the period always has to be before the end of the period. E.g. there's no need to validate that the period start date is actually before the end date.

Endless ranges

It can be useful to model a period without an actual end or an actual beginning. Imagine someone taking holidays and booking a holiday without knowing the end yet. Or providing access to a feature for a limited time, except for that one special user that has unlimited access.

Bounded ranges

However, you may also wish the range to be explicitly bounded. Postgres range bounds can be "infinite" in two ways: omitted bounds ([a,), (,b], (,)) and explicit infinity values ([a,infinity], [-infinity,b]).

We always want to forbid infinity as it's effectively unbound for both including ([infinity, infinity]) and excluding ((infinity, infinity)) ranges with infinity.

Therefore, you'll need to define for both cases when using database constraints:

Copyadd_check_constraint(
  :billing_consumption_periods,
  'NOT lower_inf(period) AND NOT upper_inf(period) AND isfinite(lower(period)) AND isfinite(upper(period))',
  name: 'period_must_be_bounded'
)