---
source: https://iinventedlight.com/library/notes/postgres-range-types.md
title: "PostgreSQL range types"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "PostgreSQL License; prose restates cited source lines"
attribution: "Summary by iinventedlight (AI-assisted). Sources: PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 8.17. Range Types, PostgreSQL License; PostgreSQL Global Development Group, PostgreSQL: Documentation: 18: 9.20. Range/Multirange Functions and Operators, PostgreSQL License."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-04
as_of: 2026-11-04
confidence: medium
untested: "No code was run for this note; only the fetched documentation pages were read."
category: coding-references
topic_area: "code-ai-eng: PostgreSQL"
tags: ['postgresql', 'sql', 'data-types', 'ranges']
sources:
  - id: S1
    title: "PostgreSQL: Documentation: 18: 8.17. Range Types"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/rangetypes.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
  - id: S2
    title: "PostgreSQL: Documentation: 18: 9.20. Range/Multirange Functions and Operators"
    publisher: PostgreSQL Global Development Group
    url: https://www.postgresql.org/docs/current/functions-range.html
    accessed: 2026-11-04
    license: "PostgreSQL License"
---
# PostgreSQL range types

**Answer:** Range types store an interval of values, like `int4range` or `tstzrange`, with inclusive `[` or exclusive `)` bounds. Operators test overlap (`&&`), containment (`@>`) and adjacency, and you can define your own range types. Multiranges hold sets of non-overlapping ranges.

```sql
CREATE TABLE booking (
  room int,
  during tstzrange
);

SELECT * FROM booking
WHERE during && tstzrange('2026-11-04 10:00', '2026-11-04 11:00');

SELECT int4range(1, 10) @> 5;    -- true
SELECT int4range(1, 5) + int4range(5, 9);  -- [1,9)
```

## Bounds

The constructor defaults to `[)`: lower inclusive, upper exclusive. Use `lower_inc`, `upper_inc`, `lower_inf` and `upper_inf` to inspect bounds.

## Notes

- The ranges must overlap or be adjacent, so that the union is a single range (but see range_merge()). (S2)
- Left-of, right-of and adjacent operators return false for empty ranges.
- `lower()` and `upper()` return NULL for empty or unbounded sides.

Pair a GiST index with an exclusion constraint to prevent overlapping bookings.

## Claims
- id: C1
  claim: You can define your own range types.
  source: S1
  evidence: "In addition, you can define your own range types"
- id: C2
  claim: lower_inc and upper_inc test bound inclusivity.
  source: S1
  evidence: "The functions lower_inc and upper_inc test the inclusivity of the lower and upper bounds"
- id: C3
  claim: If the third argument is omitted, [) is assumed.
  source: S1
  evidence: "-- If the third argument is omitted, '[)' is assumed."
- id: C4
  claim: Range union requires ranges that overlap or are adjacent.
  source: S2
  evidence: "The ranges must overlap or be adjacent, so that the union is a single range"
- id: C5
  claim: Multirange union need not overlap or be adjacent.
  source: S2
  evidence: "The multiranges need not overlap or be adjacent."

## Sources
- [S1] PostgreSQL: Documentation: 18: 8.17. Range Types. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/rangetypes.html . License: PostgreSQL License. Accessed 2026-11-04.
- [S2] PostgreSQL: Documentation: 18: 9.20. Range/Multirange Functions and Operators. PostgreSQL Global Development Group. https://www.postgresql.org/docs/current/functions-range.html . License: PostgreSQL License. Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).