PostgreSQL Advanced Filtering and Pattern Matching
1. What You'll Learn
- Combine multiple filter conditions with
AND/OR/NOT - Range and NULL filtering with
IN/BETWEEN/IS NULL - Pattern matching with
LIKE/ILIKE(PostgreSQL case-insensitive feature) SIMILAR TOand regex operators~/~*/!~/!~*- Subquery filtering with
ANY/ALL/EXISTS
2. The Story
Alice is a search-feature developer at an e-commerce platform. When a user types a keyword into the search box, the system needs to:
- Fuzzy-match against product names and descriptions
- Support case-insensitive search
- Allow power users to use regular expressions for precise search
- Filter out delisted products
- Rank results by relevance
Alice needs to master the various filtering and pattern-matching tools PostgreSQL offers to build this search system.
3. Concept: AND / OR / NOT Combinations
(1) Logical Operator Precedence
| Operator | Precedence | Description |
|---|---|---|
NOT |
Highest | Negation |
AND |
Middle | Both conditions true |
OR |
Lowest | Either condition true |
▶ Example: AND Combination
SQL
SELECT product_name, unit_price, category
FROM products
WHERE category = 'Electronics'
AND unit_price > 500
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ Example: OR Combination
SQL
SELECT product_name, unit_price, category
FROM products
WHERE category = 'Electronics'
OR category = 'Books'
OR category = 'Toys';
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(2) Controlling Logic with Parentheses
OR has lower precedence than AND; omitting parentheses can yield unexpected results.
| Form | Logical meaning |
|---|---|
a AND b OR c |
(a AND b) OR c |
a AND (b OR c) |
a AND (b OR c) — usually what you intend |
▶ Example: Parentheses Change the Logic
SQL
SELECT product_name, unit_price, category
FROM products
WHERE is_active = true
AND (category = 'Electronics' OR category = 'Books');
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(3) NOT Negation
NOT negates any boolean expression and is often paired with IN, BETWEEN, LIKE, etc.
▶ Example: NOT Negates a Condition
SQL
SELECT product_name, unit_price
FROM products
WHERE NOT category = 'Electronics'
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
4. Concept: IN / BETWEEN / IS NULL
(1) IN and NOT IN
IN checks whether a value is in a given list—equivalent to multiple ORs, but more concise and performant.
| Form | Description |
|---|---|
col IN (a, b, c) |
Equivalent to col = a OR col = b OR col = c |
col NOT IN (a, b, c) |
Equivalent to col <> a AND col <> b AND col <> c |
▶ Example: IN Filter for Multiple Categories
SQL
SELECT product_name, unit_price, category
FROM products
WHERE category IN ('Electronics', 'Books', 'Toys')
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(2) BETWEEN Range Filter
BETWEEN is inclusive of its boundaries, equivalent to >= AND <=.
| Form | Equivalent |
|---|---|
col BETWEEN a AND b |
col >= a AND col <= b |
col NOT BETWEEN a AND b |
col < a OR col > b |
▶ Example: Price-Range Filter
SQL
SELECT product_name, unit_price
FROM products
WHERE unit_price BETWEEN 100 AND 500
ORDER BY unit_price;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ Example: Date-Range Filter
SQL
SELECT order_id, customer_id, order_date, total_amount
FROM orders
WHERE order_date BETWEEN '2025-01-01' AND '2025-06-30'
AND order_status = 'completed';
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(3) IS NULL / IS NOT NULL
NULL is not equal to anything (including itself); you must use IS NULL or IS NOT NULL to test for it.
| Form | Result |
|---|---|
NULL = NULL |
NULL (not TRUE) |
NULL <> NULL |
NULL (not TRUE) |
col IS NULL |
Correct NULL test |
col IS NOT NULL |
Correct non-NULL test |
▶ Example: Find Products Missing a Discount
SQL
SELECT product_name, unit_price, discount_rate
FROM products
WHERE discount_rate IS NULL
AND is_active = true;
Output:
TEXT
count
-------
5
(1 row)
(4) The NOT IN and NULL Trap
When the NOT IN list contains NULL, the whole expression may return an empty result set.
| Expression | Result | Reason |
|---|---|---|
3 NOT IN (1, 2, NULL) |
NULL |
3 <> NULL is NULL; NULL AND ... is NULL |
3 IN (1, 2, NULL) |
NULL |
3 = NULL is NULL; FALSE OR NULL is NULL |
▶ Example: Using NOT IN Safely
SQL
-- Dangerous: NULL in subquery can break NOT IN
SELECT product_name
FROM products
WHERE category NOT IN (
SELECT category FROM categories WHERE is_active = false
);
-- Safe: use NOT EXISTS instead
SELECT p.product_name
FROM products p
WHERE NOT EXISTS (
SELECT 1 FROM categories c
WHERE c.category = p.category AND c.is_active = false
);
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
5. Concept: LIKE / ILIKE Pattern Matching
(1) LIKE Wildcards
| Wildcard | Meaning | Example |
|---|---|---|
% |
Matches any-length string (incl. empty) | 'Phone%' matches Phone, Phone Case |
_ |
Matches a single character | 'A_c' matches Arc, ABC |
▶ Example: LIKE Prefix Match
SQL
SELECT product_name, unit_price
FROM products
WHERE product_name LIKE 'Wireless%'
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ Example: LIKE Contains Match
SQL
SELECT product_name, unit_price
FROM products
WHERE product_name LIKE '%Battery%'
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(2) ILIKE: Case-Insensitive (PostgreSQL Feature)
ILIKE is a PostgreSQL extension that matches case-insensitively, equivalent to LIKE + LOWER().
| Operator | Case-sensitive | Standard SQL |
|---|---|---|
LIKE |
Sensitive | Yes |
ILIKE |
Insensitive | No (PG-only) |
▶ Example: ILIKE Search
SQL
SELECT product_name, unit_price, description
FROM products
WHERE (product_name ILIKE '%iphone%'
OR description ILIKE '%iphone%')
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(3) Escaping Wildcards
When you need to search for a literal % or _, use ESCAPE to specify an escape character.
▶ Example: Search Descriptions Containing a Percent Sign
SQL
SELECT product_name, description
FROM products
WHERE description LIKE '%100\%%' ESCAPE '\';
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
6. Concept: Regular Expression Matching
(1) Regex Operators Overview
PostgreSQL provides four regex match operators, plus the SIMILAR TO syntax.
| Operator | Case-sensitive | Description |
|---|---|---|
~ |
Sensitive | Matches regex |
~* |
Insensitive | Matches regex (case-ignored) |
!~ |
Sensitive | Does not match regex |
!~* |
Insensitive | Does not match regex (case-ignored) |
SIMILAR TO |
Sensitive | SQL-standard regex subset (supports % _ ` |
▶ Example: ~ Regex Match
SQL
SELECT product_name, unit_price
FROM products
WHERE product_name ~ '^(Wireless|Bluetooth)'
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ Example: ~* Case-Insensitive Regex
SQL
SELECT product_name, unit_price
FROM products
WHERE product_name ~* 'iphone\s*(1[0-9])?'
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(2) SIMILAR TO
SIMILAR TO sits between LIKE and POSIX regex: it supports |, [], *, +, etc., but uses the SQL-style wildcards % and _.
| Feature | LIKE | SIMILAR TO | POSIX ~ |
|---|---|---|---|
% wildcard |
Yes | Yes | No (use .*) |
_ wildcard |
Yes | Yes | No (use .) |
| ` | ` alternation | No | Yes |
[] character class |
No | Yes | Yes |
+*{m,n} quantifier |
No | Partial | Full |
▶ Example: SIMILAR TO Match
SQL
SELECT product_name, unit_price
FROM products
WHERE product_name SIMILAR TO '(Wireless|Bluetooth)%'
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ Example: !~ Excludes a Regex Match
SQL
SELECT product_name, unit_price
FROM products
WHERE product_name !~ '(Refurbished|Used)'
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
7. Concept: ANY / ALL / EXISTS Subquery Filtering
(1) ANY and ALL
| Operator | Meaning | Equivalent form |
|---|---|---|
col = ANY(array) |
Equals any value in the array | col IN (...) |
col > ANY(array) |
Greater than at least one value | — |
col > ALL(array) |
Greater than all values | — |
▶ Example: ANY Equivalent to IN
SQL
SELECT product_name, unit_price
FROM products
WHERE category = ANY(ARRAY['Electronics', 'Books', 'Toys'])
AND is_active = true;
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
▶ Example: ALL Compared to a Subquery
SQL
SELECT product_name, unit_price, category
FROM products p
WHERE unit_price > ALL (
SELECT unit_price FROM products
WHERE category = 'Books' AND is_active = true
);
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(2) EXISTS Subquery
EXISTS checks whether the subquery returns any rows; it does not care about the actual values, only whether any results exist. It is safer than IN (no NULL trap) and usually faster for correlated subqueries.
| Usage | Description |
|---|---|
EXISTS (subquery) |
TRUE if the subquery returns rows |
NOT EXISTS (subquery) |
TRUE if the subquery returns no rows |
▶ Example: EXISTS Finds Customers with Orders
SQL
SELECT customer_id, customer_name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date >= CURRENT_DATE - INTERVAL '30 days'
);
Output:
TEXT
id | name | value
----+----------+-------
1 | example | 42
(1 row)
(3) IN vs EXISTS Selection Guide
| Scenario | Recommended | Reason |
|---|---|---|
| Small subquery result set | IN |
Optimizer runs the subquery first, building a small set |
| Small outer table, large subquery table | EXISTS |
Optimizer runs the subquery per outer row, terminating early |
| Subquery may contain NULL | EXISTS |
Avoids the NOT IN NULL trap |
| Independent subquery (not correlated) | IN |
Subquery result can be materialized |
8. Flowchart: Pattern-Matching Selection
flowchart TD
A[Need pattern matching?] --> B{Case-sensitive?}
B -->|Sensitive| C{Need regex?}
B -->|Insensitive| D{Need regex?}
C -->|Simple wildcard| E[LIKE]
C -->|Need OR/char class| F[SIMILAR TO]
C -->|Full regex| G["~ operator"]
D -->|Simple wildcard| H[ILIKE]
D -->|Full regex| I["~* operator"]
E --> J[Return result]
F --> J
G --> J
H --> J
I --> J
style A fill:#e1f5fe
style J fill:#c8e6c9
9. Comprehensive Example
Alice implements the core query logic for the e-commerce search feature.
SQL
-- Step 1: Basic keyword search with ILIKE (case-insensitive)
SELECT product_id, product_name, unit_price, description
FROM products
WHERE is_active = true
AND (product_name ILIKE '%keyboard%' OR description ILIKE '%keyboard%')
ORDER BY unit_price ASC;
-- Step 2: Advanced regex search for power users
SELECT product_id, product_name, unit_price, description
FROM products
WHERE is_active = true
AND product_name ~* '(mechanical|wireless)\s*keyboard'
AND unit_price BETWEEN 50 AND 300
ORDER BY unit_price DESC;
-- Step 3: Search products in specific categories with NULL handling
SELECT product_id, product_name, unit_price, category
FROM products
WHERE category IN ('Electronics', 'Computer Accessories', 'Gaming')
AND is_active = true
AND discount_rate IS NOT NULL
AND unit_price BETWEEN 20 AND 500
ORDER BY unit_price DESC NULLS LAST;
-- Step 4: Products that have at least one completed order (EXISTS)
SELECT p.product_id, p.product_name, p.unit_price
FROM products p
WHERE p.is_active = true
AND EXISTS (
SELECT 1 FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
WHERE oi.product_id = p.product_id
AND o.order_status = 'completed'
AND o.order_date >= CURRENT_DATE - INTERVAL '90 days'
)
AND NOT EXISTS (
SELECT 1 FROM product_flags pf
WHERE pf.product_id = p.product_id
AND pf.flag_type = 'recalled'
)
ORDER BY p.unit_price DESC
FETCH FIRST 50 ROWS ONLY;
❓ FAQ
Q Is the performance gap between LIKE and ILIKE significant?
A Because ILIKE must ignore case, it cannot use a standard B-tree index. At large data volumes, use PostgreSQL's pg_trgm extension to build a GIN index that speeds up ILIKE queries.
Q What if a NOT IN subquery returns NULL?
A If the subquery result contains NULL, NOT IN as a whole may return an empty result. Solutions: 1) replace it with NOT EXISTS; 2) add WHERE col IS NOT NULL in the subquery to filter out NULLs.
Q Can BETWEEN be used on TIMESTAMP?
A Yes, but note that BETWEEN is inclusive. For a TIMESTAMP, BETWEEN '2025-01-01' AND '2025-01-31' excludes the time portion of Jan 31. Prefer >= AND < instead.
Q Which is better, SIMILAR TO or POSIX regex?
A SIMILAR TO is a SQL-standard subset—portable but limited in features. POSIX regex (the ~ operator) is fully featured and is the recommended choice in PostgreSQL projects.
Q What's the difference between ANY and IN?
A col = ANY(array) is functionally equivalent to col IN (list), but ANY accepts an array argument and arrays returned by subqueries, and supports unusual comparisons like > ANY and < ALL; IN only supports equality.
Q SELECT 1 or SELECT * in an EXISTS subquery?
A Functionally identical—the optimizer ignores the SELECT list. SELECT 1 is the traditional form and slightly terser; SELECT * is also fine, with no performance difference.
Q Why can't LIKE '%keyword%' use an index?
A The leading wildcard '%keyword' defeats the B-tree index because the index's sort order can't be exploited. Solutions: pg_trgm GIN index, full-text search (tsvector + tsquery), or a dedicated search engine.
📖 Summary
- Mind operator precedence with
AND/OR/NOT; use parentheses to control logic explicitly INsuits multi-value equality;BETWEENsuits range filters;IS NULLhandles missing valuesLIKEdoes simple wildcard matching;ILIKEis case-insensitive (PostgreSQL-only)- POSIX regex
~/~*provides full regex power;SIMILAR TOsits between LIKE and regex EXISTSis safer thanIN(no NULL trap) and usually faster for correlated subqueriesANY/ALLprovide quantified comparisons over arrays / subqueries
📝 Exercises
-
⭐ Write a query that finds products in the
productstable wherecategoryis Electronics or Books andis_active = true, sorted byunit_pricedescending. -
⭐⭐ Write a query that uses
ILIKEto search theproduct_nameanddescriptionfor a user's keyword (e.g., "wireless mouse"), while filteringunit_price BETWEEN 20 AND 200and excluding products whosediscount_rate IS NULL. -
⭐⭐⭐ Write a query that uses
EXISTSto find customers who placed an order in the last 90 days, and usesNOT EXISTSto exclude customers flagged as "suspended", sorted by customer registration time descending, showing the first 20 rows via pagination.
