PostgreSQL BETWEEN Operator

The BETWEEN operator in PostgreSQL tests whether a value falls within a range. Both endpoints are inclusive, making it a concise and readable replacement for a pair of comparison operators joined by AND. It works with numeric values, character strings (alphabetical ordering), dates, and timestamps.

Syntax

value BETWEEN low AND high

This is exactly equivalent to:

value >= low AND value <= high

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

value NOT BETWEEN low AND high
-- equivalent to: value < low OR value > high

Practical Example

Create a sample invoice table for a subscription billing system:

CREATE TABLE invoices (
  invoice_id   SERIAL PRIMARY KEY,
  customer_ref VARCHAR(20)  NOT NULL,
  amount_usd   NUMERIC(10,2) NOT NULL,
  issued_at    TIMESTAMPTZ   NOT NULL DEFAULT now(),
  due_date     DATE          NOT NULL,
  status       VARCHAR(20)   NOT NULL DEFAULT 'unpaid'
);

INSERT INTO invoices (customer_ref, amount_usd, issued_at, due_date, status) VALUES
  ('ACCT-101', 1200.00, '2026-01-15 09:00+00', '2026-02-15', 'paid'),
  ('ACCT-102',  450.00, '2026-02-01 10:30+00', '2026-03-01', 'unpaid'),
  ('ACCT-103', 3800.00, '2026-02-20 14:00+00', '2026-03-20', 'paid'),
  ('ACCT-104',  990.00, '2026-03-05 08:00+00', '2026-04-05', 'unpaid'),
  ('ACCT-105', 2500.00, '2026-03-18 11:00+00', '2026-04-18', 'paid'),
  ('ACCT-106',  150.00, '2026-04-01 16:00+00', '2026-05-01', 'unpaid');

Find invoices with amounts between $500 and $3000:

SELECT invoice_id, customer_ref, amount_usd, status
FROM invoices
WHERE amount_usd BETWEEN 500.00 AND 3000.00
ORDER BY amount_usd;

Expected result:

 invoice_id | customer_ref | amount_usd | status
------------+--------------+------------+--------
          2 | ACCT-102     |     450.00 | unpaid    ← excluded (< 500)

Wait — 450 < 500, so it is excluded. The result is:

 invoice_id | customer_ref | amount_usd | status
------------+--------------+------------+--------
          4 | ACCT-104     |     990.00 | unpaid
          5 | ACCT-105     |    2500.00 | paid
          1 | ACCT-101     |    1200.00 | paid

Find invoices issued in Q1 2026 (January–March):

SELECT invoice_id, customer_ref, amount_usd, issued_at
FROM invoices
WHERE issued_at BETWEEN '2026-01-01' AND '2026-03-31'
ORDER BY issued_at;

Verify using the equivalent comparison form:

SELECT invoice_id, customer_ref, amount_usd
FROM invoices
WHERE issued_at >= '2026-01-01'
  AND issued_at <= '2026-03-31';
-- Should return identical rows

Date vs. Timestamp Ranges

When filtering a TIMESTAMPTZ column with date literals, the upper bound is interpreted as midnight of that date:

-- This includes events up to 2026-03-31 00:00:00+00 only.
-- Events on March 31 after midnight are EXCLUDED.
WHERE issued_at BETWEEN '2026-01-01' AND '2026-03-31'

-- For full-day coverage at the upper end, use < the next day:
WHERE issued_at >= '2026-01-01'
  AND issued_at  < '2026-04-01'

NOT BETWEEN

-- Invoices with amounts outside the mid-range
SELECT invoice_id, customer_ref, amount_usd
FROM invoices
WHERE amount_usd NOT BETWEEN 500.00 AND 2000.00
ORDER BY amount_usd;

This returns invoices with amount_usd < 500 or amount_usd > 2000.

Testing with Vela

Range-based filters are frequently used in UPDATE and DELETE operations — for example, archiving invoices older than a certain date or adjusting prices in a tier. Before running such operations in production, use Vela’s database branching to validate the BETWEEN predicate against a production-data clone. Run the statement as a SELECT first, inspect the affected rows, then apply the write only after confirming the range captures exactly the right records.

Production Tips

• Both endpoints of BETWEEN are inclusive — if you need an exclusive upper bound (common for timestamp ranges), use >= low AND < high explicitly.
• For BETWEEN on timestamp columns paired with date literals, remember the upper bound is midnight. Use < 'next_day' to include the full last day.
• An index on the filtered column is used as a range scan, making BETWEEN efficient on large tables. Verify with EXPLAIN ANALYZE.
• NOT BETWEEN on a widely distributed column without an index causes a sequential scan. Add a partial index if you frequently query specific ranges.
• When the low and high bounds come from user input, validate that low <= high before executing the query to prevent empty result sets from reversed ranges.