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:
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-Built-in-Range-Data-Types-1.png" data-lazy- height="104" src="data:image/svg xml,” width=”891″>
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.
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-Built-in-Range-Data-Types-2.png" data-lazy- height="111" src="data:image/svg xml,” width=”878″>
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-Built-in-Range-Data-Types-3.png" data-lazy- height="112" src="data:image/svg xml,” width=”726″>
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-Built-in-Range-Data-Types-4.png" data-lazy- height="290" src="data:image/svg xml,” width=”725″>
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-Built-in-Range-Data-Types-5.png" data-lazy- height="196" src="data:image/svg xml,” width=”835″>
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-Built-in-Range-Data-Types-6.png" data-lazy- height="171" src="data:image/svg xml,” width=”907″>
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-Built-in-Range-Data-Types-7.png" data-lazy- height="189" src="data:image/svg xml,” width=”876″>
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-Built-in-Range-Data-Types-8.png" data-lazy- height="176" src="data:image/svg xml,” width=”941″>
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-Built-in-Range-Data-Types-9.png" data-lazy- height="189" src="data:image/svg xml,” width=”902″>
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:
<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/PostgreSQL-Built-in-Range-Data-Types-10.png" data-lazy- height="315" src="data:image/svg xml,” width=”945″>
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.
About the author
<img data-del="avatar" data-lazy-src="https://kirelos.com/wp-content/uploads/2022/06/echo/channel-logo-150×150.jpg" height="112" src="data:image/svg xml,” width=”112″>
Fahmida Yesmin
I am a trainer of web programming courses. I like to write article or tutorial on various IT topics. I have a YouTube channel where many types of tutorials based on Ubuntu, Windows, Word, Excel, WordPress, Magento, Laravel etc. are published: Tutorials4u Help.
