Postgres Range Types: When Date Ranges and Numeric Ranges Belong in the Schema

The default way to model a date range in a relational schema is two columns: start_date and end_date, with an application-side convention about whether each endpoint is inclusive. This works in the sense that it stores the data, but the database treats the columns as independent and pushes every interesting range operation—overlap, containment, adjacency, gap detection—into application code or correlated subqueries.

Postgres has had a first-class range type since version 9.2. The type encodes both endpoints plus their inclusivity plus whether the range is empty as a single typed value. The operators that come with it handle the common range questions in SQL with index support. Most teams reach for the two-column pattern out of habit and never discover the alternative; the cases where range types are the right answer are more common than the adoption rate suggests.

What range types are

The built-in range types are int4range, int8range, numrange, tsrange, tstzrange, and daterange. Each is a typed value that stores a lower bound, an upper bound, and inclusivity flags for each bound. The literal syntax uses bracket and parenthesis notation borrowed from mathematical interval notation: '[2026-01-01,2026-12-31]'::daterange for inclusive on both ends, '[2026-01-01,2026-12-31)'::daterange for inclusive lower and exclusive upper.

Empty ranges are first-class values: 'empty'::daterange is a valid range that contains nothing. Unbounded ranges are also supported with empty bounds: '[2026-01-01,)'::daterange means from 2026-01-01 with no upper limit. The full expressiveness of mathematical interval algebra is available, which is more than the two-column pattern can naturally represent.

The operators that earn the type

The interesting operators come with the type. && tests overlap. @> tests containment of a range or a point. -|- tests adjacency (two ranges that share a boundary point but do not overlap). + computes union for adjacent or overlapping ranges. * computes intersection. - computes difference for the case where the difference is itself a range.

The overlap operator alone justifies the type for most date-range applications. The two-column version of "find all events that overlap with this query range" is a four-clause WHERE expression with end-vs-start comparisons in both directions. The range version is one operator: event_range && query_range. The intent is visible in the query, the planner can use a GiST index on the range column to evaluate it in logarithmic time, and the off-by-one errors that the two-column version invites disappear because inclusivity is encoded in the type.

Exclusion constraints

The combination of range types with exclusion constraints is one of the most underused features in Postgres. An exclusion constraint enforces that no two rows in a table have ranges that overlap, with the constraint evaluated atomically at insert and update time.

CREATE TABLE room_bookings (
  id SERIAL PRIMARY KEY,
  room_id INTEGER NOT NULL,
  booking_period tstzrange NOT NULL,
  EXCLUDE USING GIST (room_id WITH =, booking_period WITH &&)
);

This single constraint replaces what would otherwise be application-side check-and-insert logic with race conditions on every write. The database guarantees that no two bookings for the same room have overlapping time periods, even under concurrent writes. The cost is one GiST index, which is the natural cost of supporting range queries at all.

When range types are wrong

Range types are not the right answer for every case where the data has a start and an end. If the application never asks range questions—if the start and end are independently queried as scalar values—then the two-column pattern is fine, and migrating adds complexity without benefit. The schema should match the questions the application actually asks of the data.

Range types also do not help when the boundaries have independent semantics. A loan with an origination date and a maturity date is two distinct events with their own timestamps and metadata, not a range in the mathematical sense. Modeling it as a range collapses the distinction and makes joins to per-endpoint metadata harder. The test is whether overlap, containment, and adjacency are operations the application will perform on this data.

Indexing range types

The default index for range types is GiST. A GiST index on a range column supports overlap, containment, and adjacency queries efficiently. The index size is comparable to a B-tree on a single column. The write cost is somewhat higher than a B-tree because GiST has more work to do on insertion, but for typical OLTP workloads the overhead is small.

SP-GiST is an alternative for some range types and can be faster for certain query patterns, particularly when the range distribution is skewed. The choice between GiST and SP-GiST is usually decided by benchmarking on actual workloads rather than from first principles, with GiST as the right default when the workload is unknown.

For exact-match queries on a range column, a B-tree on the column treated as a scalar value works, but this is rarely the right answer—if you wanted exact match you probably did not need a range type in the first place. The range type pays off precisely for the queries B-tree does not handle well.

Multirange types

Postgres 14 added multirange types, which represent ordered collections of non-overlapping ranges as a single value. datemultirange and the other multirange variants are useful for representing sets like "the dates when this room is unavailable" or "the time periods covered by these subscriptions." The operators that work on ranges generalize naturally to multiranges.

The most common use case is the result of range arithmetic—the union or difference of two ranges can produce a multirange when the result is not itself a contiguous range. Without multirange types this case had to be handled either by returning multiple rows or by losing information; with multirange types the result is a single value with the natural structure.

What range types do not replace

Range types do not replace good schema design for the underlying domain. A scheduling system with rooms, equipment, attendees, and recurring patterns has substantially more structure than "range of times" and benefits from explicit modeling of all the entities. Range types are one tool in the toolkit, not a replacement for thinking about the domain.

Range types also do not solve the time zone problem. The tstzrange type stores boundaries as absolute timestamps, which is what you want for most queries, but if the business question is "what times are blocked in the user's local time zone, accounting for DST" then you need additional application logic. The range type stores the answer; it does not derive it.

Across our four products

Our four products run on SQLite, which has no equivalent range type. We model date ranges as two columns and the application code handles overlap detection. The pattern works at our scale because the overlap queries are infrequent and small.

The Postgres migration plan includes evaluation of range types for the cases where they would simplify application code. CronPing maintenance window scheduling is the strongest candidate—the data is naturally a set of (monitor, time_period) pairs with overlap detection as the primary query. FlagBit has flag-active-period data that fits the pattern. DocuMint has invoice-period data that is mostly queried as scalars, so the two-column pattern probably stays. WebhookVault's webhook event data does not have natural range structure.

The deeper observation is that range types are one of several Postgres features that change the trade-off between database and application code. The two-column pattern with application overlap logic is the default because it works in any database; the range-type pattern works only in Postgres. Teams that have committed to Postgres have access to a richer set of primitives than the lowest-common-denominator approach uses, and the richer primitives often produce simpler and more correct code than the application-side equivalents.

Our products: DocuMint (PDF invoice generation API), CronPing (cron job monitoring with status pages), FlagBit (feature flags API for modern teams), and WebhookVault (webhook capture and replay) put these patterns into production.