PostgreSQL BETWEEN operator

Suppose, in an application system, you need to decide to classify users according to their annual income. For example, to check if a user’s annual income is between 40,100 and 120,400, you could use the following statement:

annual_income >= 40100 AND annual_income <= 120400

In this case, you can rewrite the above statement using the BETWEEN operator as follows:

annual_income BETWEEN 40100 AND 120400

PostgreSQL BETWEEN operator is used to check whether a value is in an interval.

PostgreSQL BETWEEN syntax

To check if a value is in an interval, use the BETWEEN operator:

expr BETWEEN low_value AND high_value;

Here:

• The expr is an expression or column name.
• The low_value is the start value of the interval and the high_value is the end value of the interval.
• The BETWEEN operator returns true if expr the value of is greater than or equal to the low_value and less than or equal to the high_value, and otherwise false.

The BETWEEN operator is equivalent to the following statement:

expr >= low_value AND expr <= high_value

Typically, you use the BETWEEN operator in the WHERE clause like this:

SELECT * FROM table_name
WHERE expr >= low_value AND expr <= high_value

The WHERE clause can also be used in the INSERT statement, UPDATE or DELETE statement.

You can use the combined NOT and BETWEEN to check if a value is not in a interval:

expr NOT BETWEEN low_value AND high_value;

This is equivalent to the following statement using > and <:

expr < low_value OR expr > high_value

PostgreSQL BETWEEN operator Examples

We will use the tables in the Sakila sample database for demonstration, please install the Sakila sample database in PostgreSQL first.

To retrieve films whose durations between 95 and 98 minutes from the film table, use the following statement:

SELECT
    title, length
FROM
    film
WHERE
    length BETWEEN 95 AND 98;

        title        | length
---------------------+--------
 BOUND CHEAPER       |     98
 CLUELESS BUCKET     |     95
 DRUMS DYNAMITE      |     96
 EARLY HOME          |     96
 EARRING INSTINCT    |     98
 EXPENDABLE STALLION |     97
 FEUD FROGMEN        |     98
 HORN WORKING        |     95
 INVASION CYCLONE    |     97
 LOST BIRD           |     98
 LUCKY FLYING        |     97
 MADRE GABLES        |     98
 PREJUDICE OLEANDER  |     98
 REAR TRADING        |     97
 SENSIBILITY REAR    |     98
 WISDOM WORKER       |     98

To retrieve the number of films with rents between $3 and $5 from the film table, use the following statement:

SELECT
    count(*)
FROM
    film
WHERE
    rental_rate BETWEEN 3 AND 5;

 count
-------
   336

To retrieve the number of films whose rentals are not between $3 and $5 from the film table, use the following statement:

SELECT
    count(*)
FROM
    film
WHERE
    rental_rate NOT BETWEEN 3 AND 5;

 count
-------
   664

To retrieve payments for amounts between 5 and 5.98 from the payment table, use the following statement:

SELECT
    customer_id,
    amount,
    payment_date
FROM
    payment
WHERE
    amount BETWEEN 5 AND 5.98;

 customer_id | amount |    payment_date
-------------+--------+---------------------
          42 |   5.98 | 2006-02-14 15:16:03
         208 |   5.98 | 2006-02-14 15:16:03
         216 |   5.98 | 2006-02-14 15:16:03
         284 |   5.98 | 2006-02-14 15:16:03
         516 |   5.98 | 2006-02-14 15:16:03
         560 |   5.98 | 2006-02-14 15:16:03
         576 |   5.98 | 2006-02-14 15:16:03

To retrieve payments with payment date between 2005-05-24 and 2005-05-25 from the payment table, use the following statement:

SELECT
    customer_id,
    amount,
    payment_date
FROM
    payment
WHERE
    payment_date BETWEEN '2005-05-24' AND '2005-05-25';

 customer_id | amount |    payment_date
-------------+--------+---------------------
         130 |   2.99 | 2005-05-24 22:53:30
         222 |   6.99 | 2005-05-24 23:05:21
         239 |   4.99 | 2005-05-24 23:31:46
         269 |   1.99 | 2005-05-24 23:11:53
         333 |   4.99 | 2005-05-24 23:04:41
         408 |   3.99 | 2005-05-24 23:03:39
         459 |   2.99 | 2005-05-24 22:54:33
         549 |   0.99 | 2005-05-24 23:08:07

Conclusion

The PostgreSQL BETWEEN operator is used to check if a value is in an interval, if the value is in the specified interval, the BETWEEN operator returns true, otherwise it returns false.