PostgreSQL BETWEEN Operator (original) (raw)

Last Updated : 12 Jul, 2025

The **PostgreSQL BETWEEN operator is an essential tool for filtering data within a specific range. Often used in the **WHERE clause of **SELECT, **INSERT, **UPDATE, and **DELETE statements, this operator simplifies range-based conditions, making queries faster and easier to read.

In this article, we will explain the **PostgreSQL BETWEEN operator, exploring its **syntax, **usage, and several **real-world examples to illustrate its power.

What is PostgreSQL BETWEEN Operator ?

The BETWEEN operator in **PostgreSQL is used to compare a value to a defined range, including both the **lower and **upper boundaries. It’s perfect for queries involving **number, **date, or **text ranges. When used, it retrieves records that meet the criteria specified, allowing us to retrieve exactly what we need from the database efficiently

**Syntax

value BETWEEN low AND high;

Alternatively, the same query can be written as:

value >= low AND value <= high;

Examples of PostgreSQL BETWEEN Operator

For understanding this article we will be using the **sample DVD rental database, which is explained here and can be **downloaded by clicking on this link in our **examples. Below are detailed examples that explains the flexibility of the **BETWEEN operator in **PostgreSQL

**Example 1: Querying Payment Amounts

Here we will query for the payment whose amount is between **3 USD and **5 USD, using the **BETWEEN Operator in the "**Payment" table of our **sample database.

**Query:

SELECT
customer_id,
payment_id,
amount
FROM
payment
WHERE
amount BETWEEN 3 AND 5;

**Output

PostgreSQL BETWEEN Operator Example

**Explanation:

This result shows all **payments where the **amount falls within the specified range, **3 USD to 5 USD.

**Example 2: Querying Payment Dates

Here we will query for getting the payment whose payment date is between '**2007-02-07' and '**2007-02-15' using the **BETWEEN Operator in the "**Payment" table of our sample database.

**Query:

SELECT
customer_id,
payment_id,
amount,
payment_date
FROM
payment
WHERE
payment_date BETWEEN '2007-02-07' AND '2007-02-15';

**Output

PostgreSQL BETWEEN Operator Example

**Note: When making **date queries, the date literals should be in ISO 8601 format ('**YYYY-MM-DD'). This format is **standard and ensures that **PostgreSQL correctly interprets the date values.

Important Points About PostgreSQL BETWEEN Operator

**Inclusive Boundaries: The **BETWEEN operator is inclusive. This means that it includes the boundary values specified in the range.
**Case Sensitivity with Strings: When using **BETWEEN with string data types, the comparison is case-sensitive by default.
**Excluding Values with NOT BETWEEN: We can use **BETWEEN with theNOT operatorto exclude a range of values.
**Date and Time Zone Awareness: When using **BETWEEN with date and time values that include time zones, be mindful of potential time zone differences that could impact the results.

Conclusion

The PostgreSQL **BETWEEN operator is a **powerful and **flexible tool for handling **range-based queries. Whether we’re querying **numeric, **date, or **text values, the **BETWEEN operator simplifies conditions, making our queries more **efficient and **readable. By mastering this operator, we can perform more precise **data retrieval, improve query efficiency, and better manage our **PostgreSQL **database.