Exploring Advanced PostgreSQL Data Types – Part 2

Mar 8, 2024 by Robert Gravelle

Range types offer a concise way to represent a range of values within a single database field. They find application in various domains, from temporal data to numeric intervals. In this blog article, we’ll be delving into their usage (and benefits!) using both DML/SQL statements and Navicat for PostgreSQL 16.

In PostgreSQL, range types allow for the representation of continuous ranges of values. These ranges can be of different data types such as numeric, date, or timestamp. For example, a range might represent a period of time, a set of temperatures, or a range of product prices.

Let’s consider a scenario where we want to track the duration of rentals in the free dvdrental sample database. We can utilize range types to store rental durations efficiently. Here are the statements to create and populate the new “rentals_with_rental_period” table:

CREATE TABLE rentals_with_rental_period (
    rental_id SERIAL PRIMARY KEY,
    customer_id INT,
    rental_duration INT,
    rental_period DATERANGE
);

INSERT INTO rentals_with_rental_period (customer_id, rental_duration, rental_period)
VALUES
(1, 7, '[2024-02-01, 2024-02-08]'),
(2, 5, '[2024-01-15, 2024-01-20]');
    

In Navicat, we can create our table using the Table Designer:

After creating the table, we can add data to it. Be sure to prefix the Range values with a square bracket “[” and end them with a parenthesis “)”. That tells Navicat that the values belong to a range:

In this example, the “rental_period” column stores ranges representing the start and end dates of each rental. We can easily query rentals that include a specific date using the @> operator:

Range types are not limited to temporal data. They can also be used to represent numeric intervals. For instance, imagine a scenario where a product’s price can vary within a specific range based on quantity purchased. We can use range types to model this effectively:

CREATE TABLE product_price (
    product_id SERIAL PRIMARY KEY,
    price_range NUMRANGE
);

INSERT INTO product_price (price_range)
VALUES
('[10.00, 20.00)'),
('[20.00, 30.00)'),
('[30.00, )');
    

In this example, the “price_range” column stores ranges representing the minimum and maximum prices for each product. We can query products within a specific price range using the @> operator:

SELECT * FROM product_price
WHERE price_range @> 25.00;
    

Range types in PostgreSQL offer a powerful way to represent and query continuous ranges of values. Whether dealing with temporal data, numeric intervals, or other continuous values, range types provide a concise and efficient solution. By leveraging range types, developers can enhance the expressiveness and flexibility of their database schemas, paving the way for more sophisticated applications.

Looking for an easy-to-use graphical tool for PostgreSQL database development? Navicat 16 For PostgreSQL has got you covered. Click here to download the fully functioning application for a free 14 day trial!