In PostgreSQL, the BETWEEN operator is used to find a match against a range of values in SELECT, UPDATE, INSERT or DELETE queries. The BETWEEN operator is used with the conjunction of the WHERE clause, and it returns true if the targeted match found successfully.

This write-up will show you how to use the BETWEEN operator in Postgres with the help of suitable examples. So, let’s start!

How to Use BETWEEN Operator in PostgreSQL?

The basic syntax of the Postgres BETWEEN operator will go like this:

expression BETWEEN val_1 AND val_2;

Let’s illustrate the above-given syntax step-by-step:

● The expression represents a column/value to be searched in the given range.

● The val_1 and val_2 represent a range in which the targeted value will be searched.

The BETWEEN operator will return true only if the targeted value/expression lies in the given range.

Let’s head into the practical implementation of the Postgres BETWEEN operator:

Example 1: A Basic Example of BETWEEN Operator

We have already created a “bike_details” table. Let’s run the SELECT query to enlist all the details of the bike_details table:

SELECT * from bike_details;

There are ten records in the bike_details table.

Let’s use the BETWEEN operator to fetch only those bikes whose price is between 100,000 and 130,000:

SELECT * FROM bike_details
WHERE bike_price BETWEEN 100000 AND 130000
ORDER BY bike_id ASC;

The output shows that there are six bikes whose price is between 100000 and 130000.

Example 2: A Basic Example of NOT BETWEEN Operator

The NOT BETWEEN operator in Postgres provides the contrary results as compared to the BETWEEN operator. The NOT BETWEEN operator will return all those records that did not lie in the given range.

Let’s modify example 1 a little bit and utilize the NOT BETWEEN operator instead of BETWEEN operator:

SELECT * FROM bike_details
WHERE bike_price NOT BETWEEN 100000 AND 130000
ORDER BY bike_id ASC;

The NOT BETWEEN operator returned all those records that are either less than 100,00 or greater than 130,000.

Example 3: How to Use BETWEEN Operator With Date

We have updated the bike_details table. We have inserted a new column named bike_launch_date. Let’s execute the SELECT query to see the updated table records:

SELECT * FROM bike_details;

Note: Always specify the date in ISO 8601 format (Year-month-day), i.e., “YYYY-MM-DD”.

Suppose we have to fetch those bikes that launched between the “2017-01-08” and “2021-01-01” then we will execute the following query:

SELECT * FROM bike_details
WHERE bike_launch_date BETWEEN '2017-01-08' AND '2021-01-01'
ORDER BY bike_id ASC;

The output verifies that the BETWEEN operator fetched only those bikes whose launched date is between ‘2017-01-08’ and ‘2021-01-01’.

Conclusion

The BETWEEN operator in PostgreSQL is used to find a match against a range of values in SELECT, UPDATE, INSERT or DELETE queries. The BETWEEN operator is used in conjunction with the WHERE clause, and it returns true if the desired match is found. This write-up explained several use cases of the BETWEEN operator with the help of suitable examples.