PostgreSQL BETWEEN with Example

The PostgreSQL BETWEEN operator helps us in retrieving values within a range in SELECT, UPDATE, INSERT, or DELETE statement. With the BETWEEN operator, it is possible for us to match a value against a range of values.

In this tutorial, you will learn

Syntax

Here is the syntax of the BETWEEN operator in PostgreSQL:

expression BETWEEN value-1 AND value-2;

The expression is simply a column or a calculation.

The value-1, value-2 will create a range for us to compare the expression to.

With Numeric

We need to create an example that shows how to apply the BETWEEN operator on numeric values. Consider the Price table given below:

Price:

Let us the list of all books whose price is between 200 and 280:

SELECT *
FROM Price
WHERE price BETWEEN 200 AND 280;

This will return the following:

Only two items have a price ranging between 200 and 280. Note that the two are included, that is, 200 and 280.

With Date

The BETWEEN operator can be used on date values. This means that we can specify the range of date values that we need to work with. Consider the following Employees table:

Employees:

Suppose we want to see all the employees who were employed between 2013-01-01 and 2015-01-01, we can run the following command:

SELECT *
FROM Employees
WHERE employment_date BETWEEN '2013-01-01' AND '2015-01-01';

This returns the following:

Using NOT Operator

We can combine the BETWEEN operator with the NOT operator. In such a case, the list of values that are not within the specified range will be returned.

For example, to see all the items where the price is not between 200 and 280, we can run the following query:

SELECT *
FROM Price
WHERE price NOT BETWEEN 200 AND 280;

This will return the following:

Two items with price not ranging between 200 and 280 were found. Hence, their details were returned.

Using pgAdmin

Now let’s see how these actions can be performed using pgAdmin.

With Numeric

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

From the navigation bar on the left- Click Databases.
Click Demo.

Step 3) Type the query in the query editor:

SELECT *
FROM Price
WHERE price BETWEEN 200 AND 280;

Step 4) Click the Execute button.

It should return the following:

With Date

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

From the navigation bar on the left- Click Databases.
Click Demo.

Step 3) Type the query in the query editor:

SELECT *
FROM Employees
WHERE employment_date BETWEEN '2013-01-01' AND '2015-01-01';

Step 4) Click the Execute button.

It should return the following:

Using NOT Operator

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

From the navigation bar on the left- Click Databases.
Click Demo.

Step 3) Type the query in the query editor:

SELECT *
FROM Price
WHERE price NOT BETWEEN 200 AND 280;

Step 4) Click the Execute button.

It should return the following:

Summary:

We use the BETWEEN operator to retrieve the value(s) that lie within a specified range using SELECT, UPDATE, INSERT, or DELETE statement.
The range is specified using the AND operator.
The operator can be used with numeric and date values.
When used with numeric values, it helps us retrieve values that lie within the specified range.
When used with date values, it helps us retrieve values that lie within a certain range.
When used with the NOT operator, the BETWEEN operator returns values that don’t lie within the specified range.

LOGIN

PostgreSQL BETWEEN with Example