Introduction to the PostgreSQL BETWEEN operator

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

The basic syntax of theBETWEEN operator is as follows:

value BETWEEN lowAND high;

If thevalue is greater than or equal to thelow value and less than or equal to thehigh value, theBETWEEN operator returnstrue; otherwise, it returnsfalse.

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

value >= lowAND value <= high

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

value NOT BETWEEN lowAND high

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

value < lowOR value > high

In practice, you often use theBETWEENoperator in theWHERE clause of theSELECT,INSERT,UPDATE, andDELETE statements.

PostgreSQL BETWEEN operator examples

Let’s take a look at thepaymenttable in thesample database.

1) Using the PostgreSQL BETWEEN operator with numbers

The following query uses theBETWEEN operator to retrieve payments withpayment_id is between17503 and17505:

SELECT  payment_id,  amountFROM  paymentWHERE  payment_idBETWEEN 17503 AND 17505ORDER 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 theNOT BETWEEN operator to find payments with thepayment_id not between17503 and17505:

SELECT  payment_id,  amountFROM  paymentWHERE  payment_id NOT BETWEEN 17503 AND 17505ORDER 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 isYYYY-MM-DD.

The following example uses theBETWEEN operator to find payments whose payment dates are between2007-02-15 and2007-02-20 and amount more than 10:

SELECT  customer_id,  payment_id,  amount,  payment_dateFROM  paymentWHERE  payment_date BETWEEN '2007-02-15' AND '2007-02-20'  AND amount > 10ORDER 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 theBETWEEN operator to check if a value falls within a particular range.
• Use theNOT BETWEEN operator to negate theBETWEEN operator.

Last updated onApril 19, 2024

On this page

• Introduction to the PostgreSQL BETWEEN operator
• PostgreSQL BETWEEN operator examples
• Summary