Summary: in this tutorial, you will learn how to use the PostgreSQL BETWEEN operator to match a value against a range of values.
Introduction to the PostgreSQL BETWEEN operator
We use the BETWEEN operator to match a value against a range of values. The following illustrates the syntax of the BETWEEN operator:
1 | 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 expression returns true, or vice versa.
We can rewrite the BETWEEN operator by using the greater than or equal ( >=) or less than or equal ( <=) operators as the following statement:
1 | value >= low and value <= high |
If we want to check if a value is out of a range, we use the NOT BETWEEN operator as follows:
1 | value NOT BETWEEN low AND high; |
The following expression is equivalent to the expression that uses the NOT BETWEEN operator:
1 | value < low OR value > high |
We often use the BETWEENoperator in the WHERE clause of a SELECT, INSERT, UPDATE or DELETE statement.
PostgreSQL BETWEEN operator examples
Let’s take a look at the paymenttable in the sample database.
The following query selects any payment whose amount is between 8 and 9:
1 2 3 4 5 6 7 8 9 | SELECT customer_id, payment_id, amount FROM payment WHERE amount BETWEEN 8 AND 9; |
To get any payment whose amount is not in the range of 8 and 9, you use the following query:
1 2 3 4 5 6 7 8 9 | SELECT customer_id, payment_id, amount FROM payment WHERE amount NOT BETWEEN 8 AND 9; |
If you want to check a value against of date ranges, you should use the literal date in ISO 8601 format i.e., YYYY-MM-DD. For example, to get the payment whose payment date is between 2007-02-07 and 2007-02-15, you use the following query:
1 2 3 4 5 6 7 8 9 10 | SELECT customer_id, payment_id, amount, payment_date FROM payment WHERE payment_date BETWEEN '2007-02-07' AND '2007-02-15'; |
In this tutorial, you have learned how to use PostgreSQL BETWEENoperator to select a value that is in a range of values.