info

The BETWEEN operator works the same way in any PostgreSQL database, so everything you learn here carries over wherever you run Postgres. If you're an enterprise looking for managed Postgres built for the AI era, Lakebase delivers the performance, security, and native Lakehouse integration your teams need. If you're a developer or startup who needs to ship features and scale without friction, Neon is the Postgres platform built for you.

Summary: in this tutorial, you will learn how to use the PostgreSQL BETWEEN operator to check if a value falls in a particular range of values.

Introduction to the PostgreSQL BETWEEN operator

The BETWEEN operator allows you to check if a value falls within a range of values.

The basic syntax of the BETWEEN operator is as follows:

value BETWEEN low AND high;

If the value is greater than or equal to the low value and less than or equal to the high value, the BETWEEN operator returns true; otherwise, it returns false.

You can rewrite the BETWEEN operator by using the greater than or equal ( >=) and less than or equal to ( <=) operators and the logical AND operator:

value >= low AND value <= high

If you want to check if a value is outside a specific range, you can use the NOT BETWEEN operator as follows:

value NOT BETWEEN low AND high

The following expression is equivalent to the expression that uses the NOT BETWEEN operators:

value < low OR value > high

In practice, you often use the BETWEENoperator in the WHERE clause of the SELECT, INSERT, UPDATE, and DELETE statements.

PostgreSQL BETWEEN operator examples

Let’s take a look at the paymenttable in the sample database.

payment table

1) Using the PostgreSQL BETWEEN operator with numbers

The following query uses the BETWEEN operator to retrieve payments with payment_id is between 17503 and 17505:

SELECT
  payment_id,
  amount
FROM
  payment
WHERE
  payment_id BETWEEN 17503 AND 17505
ORDER BY
  payment_id;

Output:

payment_id | amount
------------+--------
      17503 |   7.99
      17504 |   1.99
      17505 |   7.99
(3 rows)

2) Using the PostgreSQL NOT BETWEEN example

The following example uses the NOT BETWEEN operator to find payments with the payment_id not between 17503 and 17505:

SELECT
  payment_id,
  amount
FROM
  payment
WHERE
  payment_id NOT BETWEEN 17503 AND 17505
ORDER BY
  payment_id;

Output:

payment_id | amount
------------+--------
      17506 |   2.99
      17507 |   7.99
      17508 |   5.99
      17509 |   5.99
      17510 |   5.99
...

3) Using the PostgreSQL BETWEEN with a date range

If you want to check a value against a date range, you use the literal date in ISO 8601 format, which is YYYY-MM-DD.

The following example uses the BETWEEN operator to find payments whose payment dates are between 2007-02-15 and 2007-02-20 and amount more than 10:

SELECT
  customer_id,
  payment_id,
  amount,
  payment_date
FROM
  payment
WHERE
  payment_date BETWEEN '2007-02-15' AND '2007-02-20'
  AND amount > 10
ORDER BY
  payment_date;

Output:

customer_id | payment_id | amount |        payment_date
-------------+------------+--------+----------------------------
          33 |      18640 |  10.99 | 2007-02-15 08:14:59.996577
         544 |      18272 |  10.99 | 2007-02-15 16:59:12.996577
         516 |      18175 |  10.99 | 2007-02-16 13:20:28.996577
         572 |      18367 |  10.99 | 2007-02-17 02:33:38.996577
         260 |      19481 |  10.99 | 2007-02-17 16:37:30.996577
         477 |      18035 |  10.99 | 2007-02-18 07:01:49.996577
         221 |      19336 |  10.99 | 2007-02-19 09:18:28.996577
(7 rows)

Summary

  • Use the BETWEEN operator to check if a value falls within a particular range.
  • Use the NOT BETWEEN operator to negate the BETWEEN operator.