BETWEEN Operator in PostgreSQL
In PostgreSQL, the BETWEEN operator is a common SQL comparison predicate that is used to ascertain if a value is inclusive of a certain range. By providing a succinct syntax that is functionally comparable to combining a larger than or equal to (>=) operator and a less than or equal to (<=) operator with a logical AND, it essentially serves to simplify range-based conditions in SQL queries. For example, x >= an AND x <= b and x BETWEEN an AND b have the same meaning. The fact that BETWEEN clearly incorporates both the lower_bound and the upper_bound in the assessed range is a crucial feature.
A wide range of PostgreSQL data types that allow natural ordering can be used with this operator. This comprises date/time types like dates, times, and timestamps; character strings; and numeric types like integers and floating-point numbers. Because of its adaptability, BETWEEN is very good in filtering data according to a variety of parameters, like age ranges, date intervals, or lexicographical string segments.It is commonly used in SELECT statements’ WHERE clause, where it filters records according to the given range criterion. For instance, WHERE age BETWEEN 22 AND 25 would be used to choose friends who are between the ages of 22 and 25 (inclusive).
Basic Functionality and Syntax
In PostgreSQL, the BETWEEN operator is a basic comparison predicate that is used to determine whether the value of an expression falls inside a given range, including the lower and upper limits. Providing a clear and understandable syntax for specifying range-based conditions in SQL queries is its main purpose. This operator is typically expressed as expression BETWEEN lower_bound AND upper_bound. Writing expression >= lower_bound AND expression <= upper_bound, when both lower_bound and upper_bound are part of the evaluated range, is functionally similar to this formulation.
This operator is quite flexible and allows comparisons between a large number of ordered data types. These comprise character strings, date/time kinds like dates, times, and timestamps, and different numeric types like integers and floating-point numbers. In a SELECT statement, BETWEEN is typically used in the WHERE clause to select rows that meet the range criteria. PostgreSQL eliminates potential ambiguities by permitting only a restricted set of expression types as the second argument of a BETWEEN clause; for more complex sub-expressions, explicit brackets are advised to define the order of evaluation. This is in contrast to the general precedence of comparison operators like >= and = and logical operators like AND and OR.
Data Type Compatibility
PostgreSQL’s BETWEEN operator is incredibly flexible and works with a large variety of data types that allow for natural ordering. It works well for comparisons of numeric kinds, including decimals, integers, and floating-point numbers. This makes it possible to filter records according to numerical ranges, such as numbers or ages. Additionally, BETWEEN allows lexicographical comparisons by supporting character strings.
It is appropriate for querying data within particular temporal periods because of its capabilities, which includes date and time types like as dates, times, and timestamps. Filtering entries that fall within a specific date range, for instance, is simple. Since the operator is constructed using standard comparison operators (like >= and <=) and is specifically stated to work as datatype BETWEEN datatype AND datatype, it will work appropriately for any data type that can be compared.
If their constituent elements or components are equivalent, BETWEEN can also be applied to arrays, composite types, and range types in addition to these popular types. Its extensive compatibility highlights its value as a basic PostgreSQL tool for filtering and querying a variety of datasets. For all supported data types, the operator always treats the lower_bound and upper_bound as inclusive within the given range.
Usage in WHERE Clause
In PostgreSQL, the WHERE clause of a SQL query is usually where the BETWEEN operator is used to filter rows according to a given range condition . In order to determine which rows are part of the result set, the condition provided by BETWEEN must evaluate to a Boolean value when used in the WHERE clause. Functionally comparable to x >= an AND x <= b, the syntax x BETWEEN an AND b inclusively determines whether x is greater than or equal to an AND less than or equal to b.
This operator offers a more succinct and understandable substitute for numerous comparison operators connected by AND, making it easier to create range queries, such as choosing records where an age column falls between 22 and 25. Friends with ages of 22, 23, 24, or 25 might be returned by a query such as SELECT * FROM friend WHERE age BETWEEN 22 AND 25 ORDER BY firstname.
Operator Precedence
Operator precedence is important when using the BETWEEN operator in PostgreSQL, especially in complex SQL queries. PostgreSQL compare operators are evaluated in a certain order. Within conventional comparison predicates, the >= and <= operators are given the most precedence. After these, the BETWEEN operator takes precedence over the less than (<) and greater than (>) operators, while the equality operator (=) is usually the least important.
Note that PostgreSQL had slightly different operator precedence rules. NOT BETWEEN and related constructs sometimes behaved inconsistently, sometimes being interpreted with NOT precedence. In PostgreSQL these rules were modified to comply with the SQL standard and reduce confusion from different interpretation of conceptually identical constructs.
PostgreSQL limits the second parameter in a BETWEEN clause to minimise ambiguity, especially when BETWEEN interacts with the logical AND operator. For more complex sub-expressions in a BETWEEN condition, brackets should be used to clearly describe the evaluation order for predictable query results.
NOT BETWEEN Operator
PostgreSQL’s NOT BETWEEN operator filters values outside a defined inclusive range. Its main goal is to simplify conditions where an expression cannot be within lower_bound and upper_bound. The expression NOT BETWEEN lower_bound AND upper_bound syntax. Logic suggests this condition matches < lower_bound OR > upper_bound. Except for BETWEEN, NOT BETWEEN removes both boundary values from the result set if a value is less than or more than the lower_bound or upper_bound.
Like BETWEEN, NOT BETWEEN works well with PostgreSQL data types that enable natural ordering, such as numeric, text, and date/time. It is usually used in the WHERE clause of SELECT queries to filter records outside the range. Arrays, composite types, and ranges with equivalent data types can also use it.
Operator precedence was inconsistent in PostgreSQL, with NOT BETWEEN occasionally acting as NOT. Later versions of these rules were updated to comply with the SQL standard to avoid confusion and treat conceptually identical constructs consistently. To avoid ambiguity in NOT BETWEEN clauses with complex sub-expressions, utilise brackets to define the evaluation order.
BETWEEN SYMMETRIC Operator
The PostgreSQL BETWEEN SYMMETRIC operator extends the BETWEEN operator to automatically handle the order of the provided limits for more flexible range testing. The main difference from the ordinary BETWEEN operator is that the lower_bound does not need to be less than the upper_bound. If the left argument of AND is greater than the right argument, BETWEEN SYMMETRIC swaps them internally. Even with reversed limits, this implies a legitimate, non-empty range.
Postgresql uses expression BETWEEN SYMMETRIC lower_bound AND upper_bound. 2 BETWEEN SYMMETRIC 3 AND 1 would evaluate to true since (3 and 1) are switched internally to (1 and 3), and 2 is within that range. In contrast, a conventional BETWEEN clause would return false if the limits are incorrectly arranged. BETWEEN SYMMETRIC checks the range by including the sorted lower_bound and upper_bound values, like other BETWEEN variants.
BETWEEN SYMMETRIC uses ordinary comparison operators and works with numeric, string, and date/time data types. SELECT statements employ it in the WHERE clause to filter entries by this flexible range condition . The NOT BETWEEN SYMMETRIC operator negates it, returning true for values beyond the (symmetrically adjusted) range .
Query Performance and Indexes
PostgreSQL’s BETWEEN operator with indexing improves query performance. B-tree indexes efficiently perform BETWEEN operations on sortable data by handling equality and range queries. PostgreSQL query planners can use B-tree indexes on columns involved in BETWEEN conditions to quickly find relevant rows, skipping a full table scan and speeding up data retrieval.
B-tree indexes can optimise operations using the BETWEEN syntax, which equals expressions >= lower_bound and <= upper_bound [Learning PostgreSQL.pdf, postgresql-16-A4.pdf, aw_pgsql_book.pdf]. When comparing indexed columns using operators like <, <=, =, >=, >, the planner may use a B-tree index.
Index existence does not ensure utilisation. Cost-based PostgreSQL query optimisers choose indexes based on their expected I/O and CPU costs. In tiny tables, a sequential scan may be faster than an index scan due to index traversal overhead. Using a BETWEEN index can cut query latency from minutes to fractions of a second for huge tables. The planner’s conclusion depends on WHERE clause selectivity and data dispersion. For these estimates, PostgreSQL uses ANALYSE and VACUUM ANALYSE procedures to update database and column statistics. The planner may adopt an inefficient plan if these numbers are wrong.
B-tree is BETWEEN’s default and most used index type, although GiST, SP-GiST, GIN, and BRIN also offer range-based searches, especially for geometric, text search, and range kinds. Int4range, daterange, and tsrange range data types can use GiST and SP-GiST indexes to speed up overlap (&&) and containment (@>) queries.
Finally, PostgreSQL’s BETWEEN operator shows range criteria in queries. Due to its inclusiveness, compatibility with a range of data types, negation support (NOT BETWEEN), and flexible symmetric variation, it often benefits from index optimisations for effective data filtering.