How to Use the PostgreSQL Ranges Data Type on Ubuntu 20.04 Server

Introduction

A range is a sequential series of data. In a PostgreSQL database server, you can store a range of values using a mathematical interval notation that implements square brackets and parenthesis to define lower and upper bounds of all possible data elements. For instance, in a loan processing application, you can use the range datatype to store the following data:

• The minimum and maximum allowed principal for each loan product (For example, you can represent a loan with a minimum allowed principal of $5,000 and a maximum principal of $100,000 as [5000, 100000]).

• The fluctuating annual interest rate of loans. This depends on the credit score of borrowers and the economy. (For example, the [8.5, 18.6] value represents a loan with an annual interest rate that varies between 8.5% and 18.6%).

• The differing loan term (or number of installments) depending on the customers' capability. (For example, if a customer can pay a loan from 6 to 36 months, you can use a value of [6, 36] to denote the information).

Without the PostgreSQL range datatype, you need two different columns to store the lower and upper bounds of all the above scenarios. This guide explains how to implement and use the PostgreSQL range data type, run common operators, and use the different functions on a Ubuntu 20.04 server.

Prerequisites

Before you begin, be sure to:

• Deploy an Ubuntu 20.04 server.
• Use SSH to access the server as a non-root sudo user.
• Install a PostgreSQL database server.

Understanding the PostgreSQL Range Subtypes

The PostgreSQL server provides different range subtypes you can use to meet your needs. To check the availability of these subtypes in your database server, follow the steps below:

1. Log in to PostgreSQL as the user postgres.

    $ sudo -u postgres psql

   Enter the correct user password and press enter to continue.

2. Enter the following command to retrieve available range subtypes.

    postgres=# SELECT 
                   oid, 
                   typname 
               FROM pg_type 
               WHERE typname ~ 'range';

   Output:

      oid  |  typname
    -------+------------
      3904 | int4range
      3926 | int8range
      3906 | numrange
      3908 | tsrange
      3910 | tstzrange
      3912 | daterange
    ...

   Each of the PostgreSQL range subtypes represents:

   • int4range: A range of 4 bytes integer.

   • int8range: A range of 8 bytes integer.

   • numrange: A range of numeric values.

   • tsrange: A range of timestamps data without timezones.

   • tstzrange: A range of timestamps data with timezones.

   • daterange: A range of dates.

After understanding the different PostgreSQL range subtypes, create a sample database.

Create a Sample PostgreSQL Database

The PostgreSQL range subtypes explained in the previous section are useful. This section describes how to use most of these range subtypes with real-life examples as explained below.

1. Create a sample_company database.

    postgres=# CREATE DATABASE sample_company;
   

2. Switch to the new sample_company database.

    postgres=# \c sample_company;
   

3. Create a sample loans_products table.

    sample_company=# CREATE TABLE loan_products (
                         product_id SERIAL PRIMARY KEY,
                         product_name VARCHAR(50),
                         principal NUMRANGE,
                         apr NUMRANGE,
                         no_of_installments INT4RANGE,
                         validity TSRANGE
                     );

   This table stores information about the available loan products in sample bank applications. The application_id column uniquely identifies each record in the table. The SERIAL keyword instructs PostgreSQL to create auto-incremented records to avoid typing the product_ids manually.

   The product_name column identifies the various loan products in the bank. The principal and apr (annual interest rate) columns use the NUMRANGE data type, this datatype allows you to store the lower and upper bound limits of the allowed principal and interest for each loan.

   The no_of_installments column must contain a range of integers that define the repayment period of a loan in months. Then, the validity column stores a range of timestamps to check whether a loan is available when customers submit their applications.

4. Before populating data to the table, examine the mathematical interval notation below. A square bracket denotes an inclusive bound that includes the boundary points in the range. A round parenthesis represents an exclusive bound that excludes the boundary points from the range.

   • [ Starts a range and includes the starting value in the range.

   • ] Ends a range and includes the end value in the range.

   • ( Start a range and excludes the starting value from the range.

   • ) Ends a range and excludes the end value from the range.

   For example:

   • [a,b] - Includes all values between a and b in the range.

   • (a,b] - Includes all values ​​between a and b, excludes a, and includes b in the range.

   • [a,b) - Includes all values between a and b, excludes b but includes a in the range.

   • (a,b) - Includes all values between a and b and excludes both a and b from the range.

5. Populate the loan_products table as below.

    sample_company=# INSERT INTO loan_products (product_name, principal, apr, no_of_installments, validity) VALUES ('EMERGENCY LOAN', '[1000,5000]', '[8,12]', '[3,12]', '[2023-01-01 00:00, 2023-12-31 23:59]'); 
                     INSERT INTO loan_products (product_name, principal, apr, no_of_installments, validity) VALUES ('DEVELOPMENT LOAN', '[3000,10000]', '[5,10]', '[12,36]', '[2023-01-01 00:00, 2023-05-31 23:59]'); 
                     INSERT INTO loan_products (product_name, principal, apr, no_of_installments, validity) VALUES ('STUDENT LOAN', '[500,1000]', '[4,7]', '[1,3]', '[2023-01-01 00:00, 2023-12-31 23:59]'); 

   Output:

    ...
    INSERT 0 1
   

6. Query the loan_products table data.

    sample_company=# SELECT
                         product_id,
                         product_name,
                         principal,
                         apr,
                         no_of_installments,
                         validity
                     FROM loan_products;

   Output:

      product_id |   product_name   |  principal   |  apr   | no_of_installments |                   validity
     ------------+------------------+--------------+--------+--------------------+-----------------------------------------------
               1 | EMERGENCY LOAN   | [1000,5000]  | [8,12] | [3,13)             | ["2023-01-01 00:00:00","2023-12-31 23:59:00"]
               2 | DEVELOPMENT LOAN | [3000,10000] | [5,10] | [12,37)            | ["2023-01-01 00:00:00","2023-05-31 23:59:00"]
               3 | STUDENT LOAN     | [500,1000]   | [4,7]  | [1,4)              | ["2023-01-01 00:00:00","2023-12-31 23:59:00"]
      (3 rows)

With the sample database in place, proceed to learn how you can use basic PostgreSQL range operators and functions.

Basic Range Datatype Operators and Functions

You can achieve the full power of the PostgreSQL range datatype using inbuilt operators and functions. Below are the most common operators and functions.

• @> Checks if a range contains another range or element.

• lower() returns the lower bound of a given range.

• upper() returns the upper bound of a given range.

To test the above operator and functions, run the following examples:

1. Select all loan products that allow a customer to borrow $500 using the @> operator.

    sample_company=# SELECT 
                         product_id,
                         product_name,
                         principal 
                     FROM loan_products 
                     WHERE principal @> '500'::NUMERIC;

   Output:

     product_id | product_name | principal
    ------------+--------------+------------
              3 | STUDENT LOAN | [500,1000]
    (1 row)
   

2. List the minimum and maximum principal allowed for each loan product using the lower() and upper() functions.

    sample_company=# SELECT 
                         product_id,
                         product_name,
                         lower(principal) as min_principal,
                         upper(principal) as max_principal
                     FROM loan_products;

   Output:

     product_id |   product_name   | min_principal | max_principal
    ------------+------------------+---------------+---------------
              1 | EMERGENCY LOAN   |          1000 |          5000
              2 | DEVELOPMENT LOAN |          3000 |         10000
              3 | STUDENT LOAN     |           500 |          1000
    (3 rows)
   

3. Select all loan products available on the first day of June 2023 using the @> operator.

    sample_company=# SELECT 
                         product_id,
                         product_name,
                         principal,
                         validity 
                     FROM loan_products 
                     WHERE validity @> '2023-06-01'::timestamp;

   Output:

     product_id |  product_name  |  principal  |                   validity
    ------------+----------------+-------------+-----------------------------------------------
              1 | EMERGENCY LOAN | [1000,5000] | ["2023-01-01 00:00:00","2023-12-31 23:59:00"]
              3 | STUDENT LOAN   | [500,1000]  | ["2023-01-01 00:00:00","2023-12-31 23:59:00"]
    (2 rows)

Indexing

The examples in this guide only use a few records. For better query speed as your database grows, add a Generalized Search Tree-based Index (GIST) on the range columns. For instance, to create a principal column index, run the following command.

    sample_company=# CREATE INDEX principal_idx ON loan_products USING GIST (principal);

Output:

    CREATE INDEX

Conclusion

In this guide, you implemented the PostgreSQL range data type on a Ubuntu 20.04 server. You have created a sample table, populated the range columns using a mathematical interval notation, and run queries to test PostgreSQL range operators and functions.

Next Steps

To leverage PostgreSQL functions, please visit the following resources.

• How to Implement Arrays with Vultr Managed Databases for PostgreSQL.

• How to Use Vultr Managed Databases for PostgreSQL with NodeJS.