PostgreSQL Built-in Range Data Types

The sequential series of data is called range data. PostgreSQL database supports a range of data types which is a unique feature of this database. The range and multi-range data types are introduced in the PostgreSQL version 14. This data type stores the lower and the upper values. The integer or the timestamp value can be stored in the field of this data type. When it is required to store many values in a single range value then it is better to use range data type. The built-in range of data types that are supported by PostgreSQL has been mentioned below.

Range Types	Purpose
int4range	It defines the range of integers.
int4multirange	It defines the multi-range integers.
int8range	It defines the range of big integers.
int8multirange	It defines the multi-range big integers.
numrange	It defines the range of numbers.
nummultirange	It defines the multi-range of numbers.
tsrange	It defines the range of timestamp without the time zone.
tsmultirange	It defines the multi-range of timestamp without the time zone.
tstzrange	It defines the range of timestamp with the time zone.
tstzmultirange	It defines the multi-range of timestamp with the time zone.
daterange	It defines the range of date.
datemultirange	It defines the multi-range of date.

Pre-requisites:

You have to install the latest version of PostgreSQL packages on the Linux operating system before executing the SQL statements shown in this tutorial. Run the following commands to install and start the PostgreSQL:

$ sudo apt-get -y install postgresql postgresql-contrib
$ sudo systemctl start postgresql.service

Run the following command to login to PostgreSQL with root permission:

$ sudo -u postgres psql

PostgreSQL range data type examples:

Before creating any table with the Boolean data type you have to create a PostgreSQL database. So, run the following command to create a database named ‘testdb’:

# CREATE DATABASE testdb;

The following output will appear after creating the database:

A. Use of integer range data type
Create a table named ‘tbl_intRange’ in the current database with two fields. Here, the first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is int_range and the data type is INT4RANGE.

# CREATE TABLE tbl_intRange (
id SERIAL PRIMARY KEY,
int_range INT4RANGE );

The following output will appear if the table is created successfully.

Run the following INSERT query to insert three records into the tbl_intRange table:

# INSERT INTO tbl_intRange (int_range)
VALUES ('[1, 10)'::int4range),
('[1, 20)'::int4range),
('[1, 30)'::int4range);

The following output will appear after executing the above query:

Run the following SELECT query to read all records from the tbl_intRange:

# SELECT * FROM tbl_intRange;

Run the following SELECT query to read those records from the tbl_intRange WHERE int_range is greater than 12:

# SELECT * FROM tbl_intRange WHERE int_range @>12;

The following output will appear after executing the above SELECT queries:

B. Use of numeric range data type
Create a table named ‘tbl_numRange’ in the current database with two fields. Here, the first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is num_range and the data type is NUMRANGE.

# CREATE TABLE tbl_numRange (
id SERIAL PRIMARY KEY,
num_range NUMRANGE );

Run the following INSERT query to insert three records into the tbl_numRange table:

# INSERT INTO tbl_numRange (num_range)
VALUES (numrange(20, 40)),
(numrange(100, 500));

The following output will appear after executing the above queries:

Run the following SELECT query that will read all records from the tbl_numRange:

# SELECT * FROM tbl_numRange;

The following output will appear after executing the above SELECT query:

C. Use of timestamp range data type
Create a table named ‘tbl_timeRange’ in the current database with two fields. Here, the first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is timeRange and the data type is TSRANGE.

# CREATE TABLE tbl_timeRange (
id SERIAL PRIMARY KEY,
timeRange TSRANGE);

Run the following INSERT query to insert three records into the tbl_timeRange table:

# INSERT INTO tbl_timeRange ( timeRange)
VALUES ('[2022-05-20 10:20:30, 2022-05-21 10:20:15)'),
('[2022-09-13 9:30:15, 2022-09-14 11:10:20)');

The following output will appear after executing the above queries:

Run the following SELECT query that will read all records from the tbl_timeRange:

# SELECT * FROM tbl_timeRange;

The following output will appear after executing the above SELECT query:

D. Use of date range data type
Create a table named ‘tbl_dateRange’ in the current database with two fields. Here, the first field name is id which is the primary key of the table. The value of this field will be incremented automatically when a new record will insert. The second field name is date_range and the data type is DATERANGE.

# CREATE TABLE tbl_dateRange (
id SERIAL PRIMARY KEY,
date_range DATERANGE);

Run the following INSERT query to insert three records into the tbl_dateRange table:

# INSERT INTO tbl_dateRange ( date_range)
VALUES ('[2022-05-20, 2022-05-21)'),
('[2022-07-10, 2022-07-15)'),
('[2022-12-01, 2022-12-16)');

The following output will appear after executing the above queries:

Run the following SELECT query that will read all records from the tbl_dateRange:

# SELECT * FROM tbl_dateRange;

Run the following SELECT query that will read those records from the tbl_dateRange where the date value of the date_range field is greater than ‘2002-07-13’.

# SELECT *FROM tbl_dateRange WHERE date_range @>'2022-07-13'::date;

The following output will appear after executing the above queries:

Conclusion:

Different uses of range data types of PostgreSQL have been shown in this tutorial by using multiple tables. The new PostgreSQL users will be able to use the range of data types in their tables after reading this tutorial.

PostgreSQL Built-in Range Data Types

Pre-requisites:

PostgreSQL range data type examples:

Conclusion:

About the author

Fahmida Yesmin