postgres data type range - ghdrako/doc_snipets GitHub Wiki

tags:

  • database
  • postgres

Postgres data type range

Postgres offer OVERLAPS operator who worki on two dates. No need range data typem. https://hakibenita.com/postgresql-unknown-features#find-overlapping-ranges

Range

Range types are a unique feature of PostgreSQL, managing two dimensions of data in a single column, and allowing advanced processing. Range types come with implicit quality checks.

Many different data types can be ranges: numbers, dates, timestamps, and the like. Every representation of a number (and dates are just strange numbers) that can be sorted can also be a range.

Range types:

  • Time Ranges: You can use range data types to store time ranges, such as business hours, meeting times, or shifts.
  • Numeric Ranges: You can use range data types to store ranges of numeric values, such as temperature, age, or income ranges.
  • Geographic Ranges: You can use range data types to store geographic ranges, such as latitude and longitude ranges, or ranges of distances.
  • Text Ranges: You can use range data types to store ranges of text values, such as character or string ranges.

The main example is the daterange data type, which stores as a single value a lower and an upper bound of the range as a single value. This allows PostgreSQL to implement a concurrent safe check against overlapping ranges.

create table rates 
 ( 
 currency text, 
 validity daterange, 
 rate numeric, 
  exclude using gist (currency with =, validity with &&) 
 );

insert into rates(currency, validity, rate) 
 select currency, 
 daterange(date, 
 lead(date) over(partition by currency 
 order by date), 
 '[)' 
 ) 
 as validity, 
 rate 
 from raw.rates 
 order by date;

The ratę table registers the rate value for a currency and a validity period, and uses an exclusion constraint that guarantees non-overlapping validity period for any given currency:

1 exclude using gist (currency with =, validity with &&)

This expression reads: exclude any tuple where the currency is=to an existing currency in our table And where the validity is overlapping with (&&) any existing validity in our table. This exclusion constraint is implemented in PostgreSQL using a GiST index.

Formed range type
SELECT int4range(10, 20);
 int4range
----------
 [10,20)
 (1 row)

SELECT daterange('2025-10-04', '2027-05-01');
        daterange
------------------------
 [2025-10-04,2027-05-01)
 (1 row)


SELECT '[10, 19]'::int4range, '[10,20)'::int4range;
 int4range | int4range
-----------+----------
 [10,20)   | [10,20)
 (1 row)

Example 1

CREATE TABLE bookings (
  room_number int,
  reservation tstzrange,
  EXCLUDE USING gist (room_number WITH =, reservation WITH &&)
);
INSERT INTO meeting_rooms (
    room_number, reservation
) VALUES (
  5, '[2022-08-20 16:00:00+00,2022-08-20 17:30:00+00]',
  5, '[2022-08-20 17:30:00+00,2022-08-20 19:00:00+00]',
);

Example 2

CREATE TABLE t_price_range (
    id              serial,
    product_name    text,
    price           numeric,
    price_range     daterange
 );

INSERT INTO t_price_range (product_name, price, price_range) VALUES ('Apple', 1.5, '[2022-01-01, 2022-03-03]');
INSERT INTO t_price_range (product_name, price, price_range) VALUES ('Peach', 2.67, '[2055-01-01, 2022-03-03]');
 ERROR:  range lower bound must be less than or equal to
    range upper bound
 LINE 1: ..._name, price, price_range)
    VALUES ('Peach', 2.67, '[2055-01-...

Range types come with implicit quality checks. PostgreSQL ensures that the boundaries make sense. Otherwise, an error is issued

Preventing e.g. multiple concurrent reservations for a meeting room - work can be offloaded to the database with an exclusion constraint that will prevent any overlapping ranges for the same room number.

By default, GiST in PostgreSQL doesn’t support one-dimensional data types that are meant to be covered byB-tree indexes. With exclusion constraints though, it’s very interesting to extend GiST support for one-dimensional data types, and so we install the btree_gist extension, provided in PostgreSQL contrib package.

select rate 2 from rates 3 where currency = 'Euro' 4 and validity @> date '2017-05-18';

The operator @>reads contains, and PostgreSQL uses the exclusion constraint’s index to solve that query efficiently:

CREATE TABLE t_price_range (
    id              serial,
    product_name    text,
    price           numeric,
    price_range     daterange
);

Range formed

SELECT int4range(10, 20); -- 10 is included in the range, while 20 is not.

SELECT '[10, 19]'::int4range, '[10,20)'::int4range; -- different parantrsis but result the same
 int4range | int4range
-----------+-----------
 [10,20)   | [10,20)
 

SELECT daterange('2025-10-04', '2027-05-01');
INSERT INTO t_price_range (product_name, price, price_range)
    VALUES ('Apple', 1.5, '[2022-01-01, 2022-03-03]');

Querying ranges

PostgreSQL provides a rich set of operators, allowing us to quickly get a handle on the range data.

One of the most commonly used operators is the contains operator:

SELECT 17 <@ '[10, 19]'::int4range;
Operator Description Example Result
= equal int4range(1,5) = '[1,4]'::int4range t
<> not equal numrange(1.1,2.2) <> numrange(1.1,2.3) t
< less than int4range(1,10) < int4range(2,3) t
> greater than int4range(1,10) > int4range(1,5) t
<= less than or equal numrange(1.1,2.2) <= numrange(1.1,2.2) t
>= greater than or equal numrange(1.1,2.2) >= numrange(1.1,2.0) t
@> contains range int4range(2,4) @> int4range(2,3) t
@> contains element '[2011-01-01,2011-03-01)'::tsrange @> '2011-01-10'::timestamp t
<@ range is contained by int4range(2,4) <@ int4range(1,7) t
< element is contained by 42 <@ int4range(1,7) f
&& overlap (have points in common) int8range(3,7) && int8range(4,12) t
<< strictly left of int8range(1,10) << int8range(100,110) t
>> strictly right of int8range(50,60) >> int8range(20,30) t
&< does not extend to the right of int8range(1,20) &< int8range(18,20) t
&> does not extend to the left of int8range(7,20) &> int8range(5,10) t
`- -` is adjacent to `numrange(1.1,2.2) -
+ union `numrange(5,15) + numrange(10,20) [5,20)
* intersection int8range(5,15) * int8range(10,20) [10,15)
- difference int8range(5,15) - int8range(10,20) [5,10)

Multirange (Postgres 14+)

A multirange consists of one or more ranges packed together in a single column.

test=# SELECT int4multirange('{(10, 20), (30, 40)}');
  int4multirange
-------------------
 {[11,20),[31,40)}
(1 row)
test=# SELECT 33 <@ int4multirange('{(10, 20), (30, 40)}');
 ?column?
----------
 t
(1 row)
test=# SELECT 25 <@ int4multirange('{(10, 20), (30, 40)}');
 ?column?
----------
 f
(1 row)

The contains operator (<@) works normally. We can also see that those ranges are simply passed to PostgreSQL as an ordinary array. Of course, we can also check whether ranges overlap with multiranges:

test=# SELECT int4multirange('{(10, 20), (30, 40)}')
    && int4range(18, 32);
 ?column?
----------
 t
(1 row)

test=# SELECT int4multirange('{(10, 20), (15, 30)}');
 int4multirange
----------------
 {[11,30)}

The database engine has figured out that those ranges are actually one. It folded those ranges into one big range.

Usage

The main problem is that is Postgres specyfic feature and is not potrable to other database. To hide this you can use views:

 CREATE VIEW v AS
 SELECT
 id,
 product_name,
 price,
 lower(price_range),
 upper(price_range)
 FROM t_price_range;

 CREATE VIEW
 test=# \x
 Expanded display is on.
 test=# SELECT * FROM v;
-[ RECORD 1 ]+----------
id           | 1
product_name | Apple
price        | 1.5
lower        | 2022-01-01
upper        | 2022-03-04

Temporar constraint

PostgreSQL 18 introduces temporal constraints, a powerful new capability that allows us to enforce data integrity rules over time periods directly in our schema definition. Before this release, preventing overlapping time ranges for the same entity required application-level logic or hand-crafted exclusion constraints. The new WITHOUT OVERLAPS clause for PRIMARY KEY and UNIQUE constraints, along with the PERIOD clause for FOREIGN KEY constraints, brings this functionality into the database engine itself using native range types.

CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE hr.project_assignments (
  employee_id INT NOT NULL REFERENCES hr.employees(employee_id),
  project_name VARCHAR(100) NOT NULL,
  assignment_period daterange NOT NULL,
  PRIMARY KEY (employee_id, assignment_period WITHOUT OVERLAPS)
);

The WITHOUT OVERLAPS keyword tells PostgreSQL that for any given employee_id, the assignment_period ranges must never overlap. Behind the scenes, this behaves like an exclusion constraint using a GiST index, but the syntax is far cleaner and more declarative. We will now insert valid non- overlapping assignments:

INSERT INTO hr.project_assignments (employee_id, project_name,
assignment_period)
VALUES
(1, 'Database Migration', daterange('2025-01-01', '2025-06-30')),
(1, 'Cloud Infrastructure', daterange('2025-07-01', '2025-12-31')),
(2, 'API Gateway', daterange('2025-03-01', '2025-09-30'));

If we now attempt to insert an assignment for employee 1 that overlaps with an existing period, PostgreSQL will reject it immediately. At this stage, we will then run the following SQL to test the constraint:

INSERT INTO hr.project_assignments (employee_id, project_name,
assignment_period)
VALUES (1, 'Security Audit', daterange('2025-05-01',
'2025-08-31'));

The terminal shall throw the following error:

ERROR: conflicting key value violates exclusion constraint
"project_assignments_pkey"
DETAIL: Key (employee_id, assignment_period)=(1,
[2025-05-01,2025-08-31))
conflicts with existing key (employee_id,
assignment_period)=
(1, [2025-01-01,2025-06-30)).

Temporal Foreign Keys with PERIOD

PostgreSQL 18 also introduces temporal foreign keys using the PERIOD clause, which ensures referential integrity across time ranges by checking for range containment rather than simple equality. Here, we will create a project_milestones table that references project_assignments as shown below:

CREATE TABLE hr.project_milestones (
milestone_id SERIAL PRIMARY KEY,
employee_id INT NOT NULL,
milestone_date daterange NOT NULL,
milestone_name VARCHAR(100) NOT NULL,
FOREIGN KEY (employee_id, PERIOD milestone_date)
REFERENCES hr.project_assignments (employee_id, PERIOD
assignment_period)
);

The PERIOD keyword instructs PostgreSQL to verify that the milestone_date range is fully contained within a valid assignment_period for the same employee_id. If we attempt to insert a milestone that falls outside any assignment period, the database will reject it, guaranteeing temporal referential integrity without any application-level validation. The following diagram illustrates how WITHOUT OVERLAPS and PERIOD work together across two related tables.