PostgreSQL BETWEEN Query with Example

BETWEEN Operator in PostgreSQL

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

In this PostgreSQL Between Operator tutorial, you will learn:

PostgreSQL Between Query 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.

PostgreSQL Between Query 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:

PostgreSQL Between Query with Numeric

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:

PostgreSQL Between Query with Numeric

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

PostgreSQL Between Query 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:

PostgreSQL Between Query with Date

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:

PostgreSQL Between Query with Date

Postgres Between Query 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:

Postgres Between Query using NOT Operator

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

PostgreSQL Between Query using pgAdmin

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

How To Use Between Query with Numeric in PostgreSQL using pgAdmin

Here is how to use Between query with Numeric in PostgreSQL using pgAdmin:

Step 1) Login to your pgAdmin account

Open pgAdmin and Login to your account using your credentials

Step 2) Create a Demo Database

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

PostgreSQL Between Query using pgAdmin

Step 3) Type the Query

Type the below query in the query editor:

SELECT *
FROM Price
WHERE price BETWEEN 200 AND 280;

Step 4) Execute the Query

Click the Execute button

PostgreSQL Between Query using pgAdmin

It should return the following:

PostgreSQL Between Query using pgAdmin

With Date

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

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

Between Query in PostgreSQL using pgAdmin

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.

Between Query in PostgreSQL using pgAdmin

It should return the following:

Between Query in PostgreSQL using pgAdmin

Using NOT Operator

To accomplish the same through pgAdmin, do this:

Step 1) Login to your pgAdmin account.

Step 2)

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

Between Query in PostgreSQL using pgAdmin

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.

PostgreSQL Between Query using pgAdmin

It should return the following:

PostgreSQL Between Query using pgAdmin

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.

Download the Database used in this Tutorial