PostgreSQL - BETWEEN Operator

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

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

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 the NOT operator to 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.