BETWEEN

The BETWEEN operator in PostgreSQL tests whether a value falls within a range. Both endpoints are inclusive, making it a concise replacement for a pair of comparison operators joined by AND.

BETWEEN syntax

value BETWEEN low AND high

This is exactly equivalent to:

value >= low AND value <= high

To check that a value is outside a range, use NOT BETWEEN:

value NOT BETWEEN low AND high

Which is equivalent to:

value < low OR value > high

BETWEEN works with numeric values, character strings (alphabetical order), and date/timestamp values. It is most commonly used in the WHERE clause of SELECT, UPDATE, and DELETE statements.

PostgreSQL BETWEEN examples

BETWEEN with numbers — retrieve payments with IDs in a range:

SELECT payment_id, amount
FROM payment
WHERE payment_id BETWEEN 17503 AND 17505
ORDER BY payment_id;

payment_id | amount
------------+--------
     17503 |   7.99
     17504 |   1.99
     17505 |   7.99
(3 rows)

NOT BETWEEN with numbers — retrieve payments outside a range:

SELECT payment_id, amount
FROM payment
WHERE payment_id NOT BETWEEN 17503 AND 17505
ORDER BY payment_id;

BETWEEN with a date range — use ISO 8601 date literals (YYYY-MM-DD):

SELECT customer_id, payment_id, amount, payment_date
FROM payment
WHERE payment_date BETWEEN '2007-02-15' AND '2007-02-20'
  AND amount > 10
ORDER BY payment_date;

customer_id | payment_id | amount |        payment_date
-------------+------------+--------+----------------------------
         33 |      18640 |  10.99 | 2007-02-15 08:14:59.996577
        544 |      18272 |  10.99 | 2007-02-15 16:59:12.996577
        516 |      18175 |  10.99 | 2007-02-16 13:20:28.996577
        572 |      18367 |  10.99 | 2007-02-17 02:33:38.996577
(4 rows)

Important considerations for BETWEEN

• Both endpoints are inclusive: BETWEEN 1 AND 5 matches 1, 2, 3, 4, and 5. If you want an exclusive upper bound, use >= low AND < high instead.
• Date vs. timestamp ranges: when filtering a timestamp column with date literals, the upper bound is treated as midnight of that day. BETWEEN '2007-02-15' AND '2007-02-20' includes timestamps up to 2007-02-20 00:00:00, not through the end of that day. To include the full last day, use < '2007-02-21'.
• Low must be less than high: BETWEEN 10 AND 5 returns no rows because no value satisfies value >= 10 AND value <= 5. Ensure the lower bound is always the smaller value.
• Index usage: an index on the column used in BETWEEN will be used by the query planner for range scans, making BETWEEN efficient on large tables.

Reference: PostgreSQL documentation — Comparison Functions and Operators.