intro
Let's delve into the world of ranges in PostgreSQL. You will discover what ranges are, why they are supported, and how to utilize them with numerous examples.
A range is a special data type to represent spans or intervals. Thanks to the many functions and operators offered by PostgreSQL, you can perform range-based operations and efficiently handle interval-based data.
Ranges may not be suitable for highly complex data structures, such as [jsonb](https://www.dbvis.com/thetable/json-vs-jsonb-in-postgresql-a-complete-comparison/) and hstore. At the same time, they offer robust capabilities for managing and manipulating intervals directly within your database. This guide explores the essence of PostgreSQL ranges, proving their functionality and providing practical applications.
Embark on this journey to master range types in PostgreSQL and enhance your data management expertise!
What Is a Range in PostgreSQL?
A PostgreSQL range is a data type that represents an interval of values. In detail, the DBMS supports value spans – with a lower and upper bound – of several types. These include numeric, date, and timestamp ranges.
Ranges are particularly useful when dealing with continuous or discrete sets of values. Continuous ranges represent an uninterrupted interval of values. For example, a continuous numeric range could represent all the numbers between 1 and 10. On the other hand, discrete ranges represent a set of distinct values. In other words, they can only contain specific values and not the entire continuum between them. For instance, a discrete range of weekdays could represent the set {Monday, Tuesday, Wednesday}.
In addition to range types, you also have access to multiranges. A multirange is a collection of non-overlapping ranges, allowing you to store multiple ranges as a single entity and perform operations on them collectively.
PostgreSQL Range Types
PostgreSQL range types are regular data types. This means that you can use them in CREATE TABLE</a> statements and cast operations as you would with any other data type.
Let's now take a look at all the range types offered by PostgreSQL. Note that you can also define new ranges with the CREATE TYPE statement.
Integer Ranges
The integer range PostgreSQL data type for 32-bit signed integers is int4range. It allows you to define a lower and upper bound for integer values and perform operations on them. The corresponding multirange type is int4multirange.
Example:
1
SELECT * FROM users WHERE age <@ int4range(18, 30);
This query selects all rows from users where the age column falls within the range of 18 (inclusive) to 30 (exclusive). Note that <@ is the “contain” range operator. Do not worry, though. We will cover it later in the article.
Big Integer Ranges
The big integer PostgreSQL data type is int8range. It provides similar functionality to int4range, but for 64-bit signed integers. The corresponding multirange type is int8multirange.
Example:
1
SELECT * FROM employees WHERE wage <@ int8range(4000, 100000000);
This query selects all rows from employees where the wage column falls within the range of 4000 (inclusive) to 100000000 (exclusive).
Numeric Ranges
The numrange data type represents a range of arbitrary precision numeric values. Specifically, it can represent ranges of decimal numbers with varying precision and scale. The corresponding multirange type is nummultirange.
Example:
1
SELECT * FROM users WHERE score_ratio <@ numrange(1.5, 4.0);
This query selects all users whose score ratio is within the range of 1.5 to 4.0.
Timestamp Ranges
The timestamp range PostgreSQL data type is tsrange. It is useful for defining ranges of timestamps values without time zones. The corresponding multirange type is tsmultirange.
Example:
1
SELECT * FROM orders
2
WHERE created_at <@ tsrange('2023-03-14 04:00:00', '2023-03-14 10:00:00');
This query selects all orders created on March 14, 2023, between 4 am and 10 am.
Timestamp With Time Zone Ranges
The timestamp with time zone range type is tstzrange. It is pretty similar to tsrange but also includes time zone information. The corresponding multirange type is tstmultirange.
Example:
1
SELECT * FROM orders
2
WHERE created_at <@ tstzrange('2023-01-01 10:00:00+00', '2023-01-03 15:30:00+00');
This query selects all rows from the orders table whose creation time falls within the range of January 1, 2023, 10:00:00 UTC, to January 3, 2023, 15:30:00 UTC.
Date Ranges
ThePostgreSQL date range type is daterange. This enables you to define a range of dates without considering the time component. The corresponding multirange type is datemultirange.
Example:
1
SELECT * FROM users WHERE brithdate <@ daterange('1990-01-01', '2000-01-03');
This query selects all rows from users where their birthdate falls within the range of January 1, 1990, to January 3, 2000.
PostgreSQL Range Functions and Operators
There are several operators and functions available to manipulate and work with ranges and multiranges effectively. Check out the documentation page to discover them all.
Range Operators
Here are some of the most popular range operators:
Range Functions
Here are some of the most common range functions:
Constructing Ranges and Multiranges
Before seeing how to construct ranges and multiranges, you need to understand that every non-empty range has two boundaries:
All points between these two values are included in the range.
An inclusive bound is represented by ( and means that the boundary value itself is included in the range. The exclusive bound is represented by [ and means that the boundary value is not included in the range.
That being said, there are two ways to create ranges and multiranges in PostgreSQL. Let's dig into both.
1. Through Range Literal Constants
A range literal constant is a textual representation of a range value. This syntax gives you the ability to explicitly specify the inclusivity or exclusivity of the lower and upper bounds.
To create ranges with constants, take a look at the example below:
1
-- range that includes 1, does not include 9, and does include all points in between
2
SELECT '[1,9)'::int4range;
1
-- range that does not include either 4 or 7, but includes all points in between
2
SELECT '(4,7)'::int4range;
1
-- range that includes only 2
2
SELECT '[2,2]'::int4range;
Multiranges can also be created using range literal constants. Simply provide multiple ranges between { and } separated by commas as follows:
1
-- empty multirange
2
SELECT '{}'::int4multirange;
1
-- multirange with a single element
2
SELECT '{[1,9)}'::int4multirange;
1
-- multirange with two elements
2
SELECT '{[2,5), [3,9)}'::int4multirange;
2. Via Constructors
Each range type in PostgreSQL has a corresponding constructor function with the same name as the range data type. Using the constructor function is usually more convenient than writing a range literal constant. This is because it avoids the need for extra quotes between the boundary values.
By default, the constructors consider the lower bound as inclusive and the upper bound as exclusive.
1
-- this range corresponds to [1, 10)
2
SELECT int4range(1, 10);
The optional third argument of the constructors allows you to specify the inclusivity or exclusivity of the bounds explicitly. In detail, it accepts one of the following strings (), (], [), or []:
1
-- this range corresponds to (2, 9]
2
3
SELECT int8range(2, 9, '(]');
Constructor functions also exist for creating multiranges. They take zero or more range values of the appropriate type as arguments:
1
-- a multirange containing [1.0, 14.2), [20.0, 25.5]
2
3
SELECT nummultirange(numrange(1.0, 14.2), numrange(20.0, 25.5, '[]'));
Conclusion
In this article, you learned what a PostgreSQL data type is and why it is a powerful tool. In particular, you saw how to use range operators and functions. Keep in mind that ranges are not the easiest data types to deal with. The best way to deal with them is through a full-featured SQL client with PostgreSQL support, such as DbVisualizer. This cutting-edge tool helps you to visually explore your tables, write optimized queries, and take your database management to the next level. Download DbVisualizer for free today!
FAQs
Let’s answer some questions related to the topic of the guide.
What is the difference between a range and a multirange?
In PostgreSQL, a range represents a single span between two points. Instead, a multirange is an ordered list of non-contiguous, non-empty, non-null ranges. Ranges are used to represent intervals, while multiranges allow for storing and manipulating multiple intervals as a single entity. Ranges have two bounds, while multiranges can contain zero or more ranges.
How to check range of characters in PostgreSQL?
There is no built-in range type specifically designed for characters. However, you can achieve character range checks using other data types and comparison operators. One approach is to use the BETWEEN operator. For example, to check if a character is within the range a to f, you can use the following query:
1
SELECT 'c' BETWEEN 'a' AND 'f';
This query will return true.
What is an example of a PostgreSQL date range query?
You use the PostgreSQL date range operator to perform advanced queries. For example, to find events that overlap with a specific date range, you write:
1
SELECT *
2
FROM events
3
WHERE start_date && daterange('2023-06-01', '2023-06-30');
This query will return events that have at least one day in common with the date range 2023-06-01 to 2023-06-30.
Can you define your own range types in PostgreSQL?
Yes, you can define your own PostgreSQL range types using the CREATE TYPE statement. For example, you can create a float8range type with:
1
CREATE TYPE float8range AS RANGE (
2
subtype = float8,
3
subtype_diff = float8mi
4
);
This helps you specify new types tailored to your specific needs, such as defining ranges for custom data types or specialized range semantics.
Can PostgreSQL ranges have infinite bounds?
Yes, PostgreSQL ranges can have infinite bounds. You can represent unbounded lower and upper bounds by using the special values -infinity and +infinity in the constructors or omitting one of the two boundary values in the literal strings.
For example, you can define a range of timestamps where the lower bound is unbounded and the upper bound is set to a specific date with:
1
SELECT tsrange('-infinity', '2023-06-30 10:00:00');
Or with:
1
SELECT '(, "2023-06-30 10:00:00")'::tsrange;
This will produce [-infinity,"2023-06-30 10:00:00"), representing all timestamps starting from the beginning of time up to and including June 30, 2023, at 10 am.
