Basics
- How to Insert
- How to Update
- How to Delete
- How to Trim Strings
- How to Use substring()
- How to Use substring() with RegEx to Extract a String
- How to Replace Substrings
- How to Modify Arrays
- How to Compare Arrays
- How to Concatenate Strings
- How to Convert the Case of a String
- How to Create an Array
- How to Insert Data Into an Array
- How to Query Arrays
- How to Use string_agg()
Database Management
- How to Create a Table
- How to Drop a Table
- How to Rename a Table
- How to Truncate a Table
- How to Duplicate a Table
- How to Add a Column
- How to Change a Column Name
- How to Add a Default Value
- How to Remove a Default Value From a Column
- How to Add a Not Null Constraint
- How to Remove a Not Null Constraint
- How to Add an Index
- How to Drop an Index
- How to Create a View
- How to Drop a View
- How to Reset Sequence
- How to Drop a Column
Dates and Times
Analysis
- How to Use nullif()
- How to Use Lateral Joins
- How to Calculate Percentiles
- How to Get the First Row per Group
- How to Use generate_series to Avoid Gaps in Data
- How to Do Type Casting
- How to Write a Common Table Expression
- How to Import a CSV using Copy
- How to Compare Two Values When One Is Null
- How to Use Coalesce
- How to Write a Case Statement
- How to Use Filter to Have Multiple Counts
- How to Calculate Cumulative Sum-Running Total
- How to Query a JSON Column
How to Use BETWEEN Correctly in PostgreSQL
The `BETWEEN` operator in PostgreSQL is used to filter the result set within a specific range. It’s commonly used for filtering values in numeric, text, or date columns, but there are some important nuances to consider when using it in your queries. This tutorial will guide you through the correct usage of `BETWEEN`, its common pitfalls, and examples to help you write better SQL queries.
1. Basic Usage of BETWEEN
The syntax for using `BETWEEN` is simple:
SELECT * FROM table_name WHERE column_name BETWEEN value1 AND value2;This query will return all rows where the value of `column_name` is between `value1` and `value2`, inclusive.
2. Numeric Range Example
If you want to filter records within a numeric range, you can use `BETWEEN` to select values between two numbers. For instance, let's assume we have a table `products` and we want to find products that cost between $50 and $100:
SELECT * FROM products WHERE price BETWEEN 50 AND 100;This query will return products priced from $50 to $100, including both endpoints.
3. Date Range Example
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-03-31';This query will return all orders that fall within this date range, including the boundary dates.
4. Important Notes on Inclusive Behavior
One of the key things to remember about `BETWEEN` is that it is inclusive. This means that both `value1` and `value2` are included in the result set. For example, the range `BETWEEN 50 AND 100` includes both 50 and 100.
5. Using NOT BETWEEN
You can use `NOT BETWEEN` to exclude a range. For example, if you want to find products that do not cost between $50 and $100, you can use:
SELECT * FROM products WHERE price NOT BETWEEN 50 AND 100;This query will return products that are priced either below $50 or above $100.
6. Common Pitfalls
- Data Types Matter: Ensure that the data types of the column and the values in the `BETWEEN` statement are compatible. For example, trying to compare a date to a string may lead to unexpected results.
- Range Direction: Always ensure that the lower bound comes first in the `BETWEEN` clause. `BETWEEN 100 AND 50` is invalid and will result in no results being returned.
Conclusion
Using `BETWEEN` in PostgreSQL is a simple and effective way to filter data within a range. However, it's important to remember that `BETWEEN` is inclusive and that the data types and order of the range values matter. By keeping these tips in mind, you can avoid common mistakes and write more accurate queries in PostgreSQL.