← All notes

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.

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).

@misc{iil_note_q93gqkw0,
  author = {{iinventedlight}},
  title = {{PostgreSQL range types}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/postgres-range-types/},
  note = {Permanent ID iil:note:q93gqkw0. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/postgres-range-types.md.