Advanced Filtering: Pattern Matching with LIKE & Wildcards

This chapter covers the core concepts of advanced filtering in PostgreSQL. It focuses on pattern matching using the LIKE and ILIKE operators, working with wildcards, and setting up a robust database schema with diverse data types (including JSONB and arrays) to run real-world queries.

1. Why Do We Need Advanced Filtering?

In real-world applications, database queries rarely rely solely on exact matches (e.g., WHERE email = 'john.doe@gmail.com'). Users frequently search using partial information, such as:

• Searching for users whose names start with "Ar".
• Filtering out all email addresses belonging to a specific domain (e.g., ending with @gmail.com).
• Finding courses that contain the word "PostgreSQL" anywhere in their title.

Using the standard equality operator (=) fails in these scenarios because it requires an exact, character-for-character match. Advanced filtering operators like LIKE and ILIKE, combined with wildcards, allow you to perform flexible, pattern-based searches across your text columns.

2. Database Setup & Schema Design

To practice advanced filtering, we will set up a clean schema in our school database. This schema utilizes a variety of PostgreSQL data types, including numeric types for financial precision, booleans, dates, semi-structured JSONB, and arrays.

The Mental Model of Our Schema

Step-by-Step Database Setup in pgAdmin

1. Open pgAdmin and connect to your PostgreSQL server.
2. Locate and click on the school database.
3. Open the Query Tool from the top menu.
4. Execute the following SQL script to create the table:

CREATE TABLE enrollments (
    enrollment_id SERIAL PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    course_name VARCHAR(100) NOT NULL,
    level VARCHAR(50) NOT NULL,
    price NUMERIC(10, 2) NOT NULL,
    enrolled_on DATE NOT NULL,
    completion BOOLEAN DEFAULT FALSE,
    rating INTEGER CHECK (rating >= 1 AND rating <= 5),
    course_meta JSONB,
    skills VARCHAR(50)[]
);

Note on pgVector: If your initial script contains a VECTOR data type at the end, remove it. Using the VECTOR data type requires the pgvector extension to be installed and enabled on your PostgreSQL instance. For this standard setup, we omit it to avoid configuration errors.

Understanding the Data Types Used:

• SERIAL: An auto-incrementing integer sequence used to automatically generate unique primary keys.
• NUMERIC(10, 2): A fixed-point type that stores exact numbers. This is highly recommended for monetary values (like price) to prevent the rounding errors inherent in floating-point types (REAL or DOUBLE PRECISION).
• JSONB: Binary-format JSON data. Unlike standard JSON, JSONB decompresses the input document into a binary format, making it slightly slower to write but significantly faster to query and index.
• VARCHAR(50)[]: A one-dimensional array of variable-length character strings, allowing us to store multiple skills within a single column.

3. Inserting Sample Dataset

Run the following query to populate the enrollments table with diverse records. This dataset includes variations in letter casing, domains, and course levels to help us test our pattern-matching queries.

INSERT INTO enrollments (
    student_name, email, course_name, level, price, enrolled_on, completion, rating, course_meta, skills
) VALUES 
('Amit Sharma', 'amit.sharma@gmail.com', 'Mastering PostgreSQL', 'Advanced', 199.99, '2023-10-01', true, 5, '{"duration": "12 weeks", "instructor": "Dr. Raj"}', ARRAY['SQL', 'PostgreSQL', 'Database Design']),
('Priya Patel', 'priya.patel@yahoo.com', 'Python for Beginners', 'Beginner', 49.99, '2023-10-05', false, 4, '{"duration": "6 weeks", "instructor": "Sarah Jenkins"}', ARRAY['Python', 'Programming']),
('Arjun Verma', 'arjun.v@outlook.com', 'Advanced React & Redux', 'Advanced', 149.99, '2023-09-15', true, 5, '{"duration": "10 weeks", "instructor": "Michael Chang"}', ARRAY['JavaScript', 'React', 'Redux']),
('Ananya Rao', 'ananya.rao@gmail.com', 'Introduction to SQL', 'Beginner', 29.99, '2023-10-12', true, 3, '{"duration": "4 weeks", "instructor": "Dr. Raj"}', ARRAY['SQL', 'Data Analysis']),
('Rohan Das', 'rohan.das@gmail.com', 'Node.js Microservices', 'Intermediate', 99.99, '2023-10-10', false, 4, '{"duration": "8 weeks", "instructor": "Alex Mercer"}', ARRAY['JavaScript', 'Node.js', 'Docker']),
('Siddharth Malhotra', 'sid.malhotra@gmail.com', 'PostgreSQL Performance Tuning', 'Advanced', 249.99, '2023-08-20', false, 5, '{"duration": "14 weeks", "instructor": "Dr. Raj"}', ARRAY['SQL', 'PostgreSQL', 'Performance Tuning']);

4. Core Mental Model of Pattern Matching

Pattern matching in SQL allows you to search for strings that conform to a specific structure. PostgreSQL provides two primary wildcards for this purpose:

Real-Life Analogy: The Filing Cabinet

Imagine a physical filing cabinet containing thousands of student folders sorted alphabetically.

• If you ask for folders labeled "Am%", the clerk will pull out every folder starting with "Am" (e.g., "Amit", "Amanda", "Amelia").
• If you ask for folders labeled "An_nya", the clerk will look for folders that are exactly 7 characters long, starting with "An", followed by any single character, and ending with "nya" (matching "Ananya", but ignoring "Anannyya").

5. Naive vs. Advanced Filtering Approaches

The Naive Approach (Exact Matching)

If we want to find all students enrolled in a PostgreSQL-related course, we might write:

-- This query only returns exact matches
SELECT student_name, course_name 
FROM enrollments 
WHERE course_name = 'PostgreSQL';

Why this fails: This query returns zero rows because our courses are named 'Mastering PostgreSQL' and 'PostgreSQL Performance Tuning'. None of them are named exactly 'PostgreSQL'.

The Advanced Approach (Using LIKE and %)

To find any course containing "PostgreSQL" anywhere in its title, we wrap the search term with % wildcards:

SELECT student_name, course_name 
FROM enrollments 
WHERE course_name LIKE '%PostgreSQL%';

Result Set:

6. Step-by-Step Query Evolution

Let's explore how to construct queries using different wildcard combinations and case-sensitivity settings.

Scenario A: Finding Prefixes (Starts With)

To find all students whose names start with the letter 'A':

SELECT student_name, email 
FROM enrollments 
WHERE student_name LIKE 'A%';

• Pattern: 'A%' matches 'Amit Sharma', 'Arjun Verma', and 'Ananya Rao'.

Scenario B: Finding Suffixes (Ends With)

To find all students using a Gmail address:

SELECT student_name, email 
FROM enrollments 
WHERE email LIKE '%@gmail.com';

• Pattern: '%@gmail.com' matches any string of characters that terminates exactly with @gmail.com.

Scenario C: Case-Insensitive Matching (ILIKE)

Standard SQL LIKE is strictly case-sensitive. If a user searches for 'postgresql' in lowercase, a standard LIKE query will return no results.

-- Returns 0 rows because of case mismatch
SELECT student_name, course_name 
FROM enrollments 
WHERE course_name LIKE '%postgresql%';

To solve this, PostgreSQL provides the ILIKE operator, which ignores case differences:

-- Successfully returns both PostgreSQL courses
SELECT student_name, course_name 
FROM enrollments 
WHERE course_name ILIKE '%postgresql%';

Scenario D: Single Character Matching (_)

Suppose we want to find students whose names have a specific pattern where we only want to allow a single wildcard character. For example, finding emails that have exactly one character between 'arjun.' and '@outlook.com':

SELECT student_name, email 
FROM enrollments 
WHERE email LIKE 'arjun._@outlook.com';

• Pattern: 'arjun._@outlook.com' matches 'arjun.v@outlook.com' because _ matches exactly one character (v).

7. Comparison of Pattern Matching Operators

PostgreSQL supports several ways to perform pattern matching. The table below outlines their differences:

8. Real-World Pitfalls & Debugging Workflows

Pitfall 1: Leading Wildcards and Performance Degradation

When you run a query with a leading wildcard (e.g., LIKE '%PostgreSQL'), PostgreSQL cannot use standard B-Tree indexes on that column.

Mitigation Strategy:

• Avoid leading wildcards if possible. If you only need prefix matching, use LIKE 'PostgreSQL%' (which can utilize indexes).
• If you must search with leading wildcards on large datasets, implement a trigram index (pg_trgm extension) to speed up the search:
  SQL

  Copy

  CREATE EXTENSION IF NOT EXISTS pg_trgm;
  CREATE INDEX idx_course_name_trgm ON enrollments USING gin (course_name gin_trgm_ops);
  

Pitfall 2: Escaping Literal Wildcards

What if you want to search for a literal % or _ character inside a column? For example, searching for a discount code named WINTER_20 where the underscore is literal, not a wildcard.

The Solution (The ESCAPE Clause): Use an escape character (typically a backslash \) to tell PostgreSQL to treat the wildcard as a literal character:

-- Finds courses containing a literal underscore
SELECT course_name 
FROM enrollments 
WHERE course_name LIKE '%\_%' ESCAPE '\';

9. Complete Query Execution Flow

The diagram below illustrates how a pattern-matching query is processed by the PostgreSQL database engine:

Summary and Key Takeaways

• Pattern matching is essential for handling partial, fuzzy, or user-defined search inputs.
• LIKE is standard, case-sensitive SQL pattern matching.
• ILIKE is a PostgreSQL-specific, case-insensitive pattern matching operator.
• The % wildcard matches any sequence of zero or more characters.
• The _ wildcard matches exactly one single character.
• NUMERIC should always be used instead of floating-point types for monetary values to ensure mathematical precision.
• JSONB is the preferred format for storing semi-structured JSON data in PostgreSQL due to its indexing capabilities and faster query performance.
• Performance Warning: Queries using leading wildcards (e.g., LIKE '%term') bypass standard B-Tree indexes, resulting in full table scans. Use Trigram indexes (pg_trgm) to optimize these queries on large datasets.
• Use the ESCAPE clause to search for literal % or _ characters within your text fields.