Pattern Matching and Text

LIKE for simple patterns, tsvector/tsquery for language-aware search

A user types "hoppy" into the beer search box. LIKE '%hoppy%' works for exact substring matches. But what if the description says "hop-forward" or "intensely hopped"? LIKE misses those. And what about case? LIKE '%Hoppy%' misses "hoppy".\n\nPostgreSQL has two tools for text search: LIKE/ILIKE for exact pattern matching, and the full-text search system (tsvector + tsquery) for language-aware search that handles stemming, stop words, and ranking. The beer_db already has description_tsv columns ready to use.

LIKE and ILIKE: Simple Pattern Matching

-- % matches any sequence of characters
SELECT name FROM beers WHERE name LIKE 'Premium%';  -- starts with "Premium"
SELECT name FROM beers WHERE name LIKE '%Gose';     -- ends with "Gose"
SELECT name FROM beers WHERE name LIKE '%Marzen%';  -- contains "Marzen"

-- _ matches exactly one character
SELECT name FROM beers WHERE name LIKE '_old%';     -- second char is 'o'

-- ILIKE is case-insensitive
SELECT name FROM beers WHERE name ILIKE '%gose%';   -- matches "Gose", "GOSE", "gose"

Why Leading Wildcards Are Expensive

WHERE description LIKE 'Czech%'    -- ✅ can use an index prefix scan
WHERE description LIKE '%Czech%'   -- ❌ must scan every row — no index helps

A leading % means "could start with anything" — so PostgreSQL cannot use a B-tree index and falls back to a sequential scan of every row in the table.

Full-Text Search: Language-Aware Matching

The beers.description_tsv column is a pre-computed tsvector that PostgreSQL updates automatically on every INSERT/UPDATE. It stores the stemmed, normalized lexemes from the description.

-- Find beers whose description contains words related to "hop"
-- (matches hop, hops, hopped, hopping — anything with the same stem)
SELECT name, description
FROM beers
WHERE description_tsv @@ to_tsquery('english', 'hop');

-- AND: both terms must appear
WHERE description_tsv @@ to_tsquery('english', 'hop & malt');

-- OR: either term
WHERE description_tsv @@ to_tsquery('english', 'tropical | citrus');

-- NOT: exclude a term
WHERE description_tsv @@ to_tsquery('english', 'hop & !bitter');

Full-text search uses the GIN index on description_tsv — it is fast even on large tables.

tsvector and tsquery

A tsvector is a sorted list of normalised lexemes (word stems) extracted from a text value. A tsquery is a search expression using those same lexemes. The @@ operator tests whether a tsvector satisfies a tsquery. PostgreSQL applies stemming (hop → hop), stop-word removal (a, the, is), and normalisation (HOPS → hop) automatically.

🔌 Connect to beer_db

Connect to beer_db as the beer_db user.

psql -U beer_db -d beer_db

psql (18.4) Type "help" for help. beer_db=>

🔤 LIKE: Find Beers by Name Pattern

Find all beers whose name starts with "Premium" using LIKE. Then find all beers whose name contains "Gose" (anywhere in the name).

SELECT name, style FROM beers WHERE name LIKE 'Premium%';

SELECT name, style FROM beers WHERE name LIKE '%Gose%';

🔡 ILIKE: Case-Insensitive Search

Search for beers whose name contains "marzen" in any case using ILIKE. Confirm that LIKE would miss lowercase variants.

SELECT name FROM beers WHERE name ILIKE '%marzen%';

name ----------------- Special Marzen Premium Marzen

📖 Full-Text Search: Find Beers by Description Theme

Use the pre-built description_tsv column to find beers whose description relates to "tropical" flavours. Full-text search will match "tropical" and its stemmed variants, using the GIN index automatically.

SELECT name, description FROM beers WHERE description_tsv @@ to_tsquery('english', 'tropical') LIMIT 5;

name | description --------------+------------------------------------------------------------- Seasonal Ale | Dry-hopped with Galaxy and Nelson Sauvin for a tropical, wine-like hop character.

🔗 Full-Text AND vs OR: Precision vs Recall

First, find beers whose description mentions both "citrus" AND "hop" using &. You will get zero rows — no beer in the dataset has both terms in its description simultaneously. Then swap & for | (OR) and run again. OR requires only one of the terms to appear, so any beer mentioning either "citrus" or "hop" is returned. This is the core tradeoff in search: AND gives precision (every term must match), OR gives recall (any term is enough).

SELECT name, description FROM beers WHERE description_tsv @@ to_tsquery('english', 'citrus & hop');

SELECT name, description FROM beers WHERE description_tsv @@ to_tsquery('english', 'citrus | hop');

-- AND: both terms required — no beer qualifies name | description -------+------------- (0 rows) -- OR: either term sufficient — all hop-forward beers appear name | description -------------------+---------------------------------------------------------------------------------------- Seasonal Ale | Dry-hopped with Galaxy and Nelson Sauvin for a tropical, wine-like hop character. Crisp Weizen | Dry-hopped with Galaxy and Nelson Sauvin for a tropical, wine-like hop character. Limited Sour | Dry-hopped with Galaxy and Nelson Sauvin for a tropical, wine-like hop character. Barrel-Aged Stout | An intensely hoppy IPA bursting with tropical fruit aromas of mango and passion fruit. Hazy IPA | An intensely hoppy IPA bursting with tropical fruit aromas of mango and passion fruit. (5 rows)

🎭 ts_headline: The Database Highlights Matches for You

Run ts_headline on the description column to get a search-result snippet with matched terms wrapped in  tags — straight out of the database, no application code needed. Search for beers matching "tropical & fruit" and see the HTML appear in the output.

SELECT name, ts_headline('english', description, to_tsquery('english', 'tropical & fruit'), 'MaxWords=15, MinWords=5') AS snippet FROM beers WHERE description_tsv @@ to_tsquery('english', 'tropical & fruit') LIMIT 5;

name | snippet -------------------+---------------------------------------------- Barrel-Aged Stout | tropical fruit aromas of mango Hazy IPA | tropical fruit aromas of mango Crisp IPA | tropical fruit aromas of mango Wild IPA | tropical fruit aromas of mango Bold Sour | tropical fruit aromas of mango (5 rows)

🧬 tsvector_to_array: See What the Index Actually Stores

Run tsvector_to_array on the description_tsv column for "Seasonal Ale". You will see the raw lexemes the GIN index holds: stemmed, lowercased, stop-words removed. Find "tropic" in the array — that is why searching "tropical" matched this row.

SELECT name, tsvector_to_array(description_tsv) AS lexemes FROM beers WHERE name = 'Seasonal Ale';

name | lexemes --------------+-------------------------------------------------------------------------- Seasonal Ale | {charact,dri,dry-hop,galaxi,hop,like,nelson,sauvin,tropic,wine,wine-lik} Seasonal Ale | {amber,balanc,caramel,color,deep,earthi,hop,malt,sweet,toffe} (2 rows)

🎯 OR vs AND: One Character, 62 Times More Results

Run the same search twice — first with & (AND), then with | (OR) — for "tropical | citrus | fruit". Watch the result count jump from 0 to 62 by changing a single character. AND requires every term to appear in the same document. OR requires any one of them.

SELECT name FROM beers WHERE description_tsv @@ to_tsquery('english', 'tropical & citrus & fruit');

SELECT name FROM beers WHERE description_tsv @@ to_tsquery('english', 'tropical | citrus | fruit');

-- AND: all three terms required name | description ------+------------- (0 rows) -- OR: any one term sufficient name -------------------- Seasonal Ale Wild Bock Wild Marzen Roasty Lager Vintage Wit ... Golden Saison Wild Gose Limited Weiss (62 rows)

📐 phraseto_tsquery vs `<->`: Two Syntaxes, One Result

Run both phraseto_tsquery('english', 'passion fruit') and to_tsquery('english', 'passion <-> fruit') and compare the output. They produce identical tsquery values. phraseto_tsquery is the ergonomic wrapper — <-> is the raw DSL operator it generates.

SELECT phraseto_tsquery('english', 'passion fruit');

SELECT to_tsquery('english', 'passion <-> fruit');

phraseto_tsquery ----------------------- 'passion' <-> 'fruit' (1 row) to_tsquery ----------------------- 'passion' <-> 'fruit' (1 row)

🌿 Stemming: Search "tropical", Match via Lexeme "tropic"

First run tsvector_to_array on "Crisp IPA" and find "tropic" in the lexeme array — even though the original description says "tropical". The English stemmer stripped the suffix at index time. Then search to_tsquery('english', 'tropical') and watch Crisp IPA appear in the results — because Postgres stems your search term to "tropic" too, and the two meet in the middle. This is why you never need ILIKE '%tropical%' OR ILIKE '%tropics%' chains.

SELECT tsvector_to_array(description_tsv) AS lexemes FROM beers WHERE name = 'Crisp IPA';

SELECT name FROM beers WHERE description_tsv @@ to_tsquery('english', 'tropical') LIMIT 5;

-- Step 1: inspect the lexemes — "tropical" was stored as "tropic" lexemes ----------------------------------------------------------- {aroma,burst,fruit,hoppi,intens,ipa,mango,passion,tropic} (1 row) -- Step 2: search "tropical" — Postgres stems it to "tropic" and finds the match name ------------------- Seasonal Ale Crisp Weizen Limited Sour Barrel-Aged Stout Hazy IPA (5 rows)

Lab complete! You have two tools for text search — and know when to use each:\n\n\n LIKE 'pattern%' : ✅ prefix match, index-friendly\n LIKE '%pattern%' : ⚠️ sequential scan — avoid on large tables\n ILIKE : ✅ case-insensitive, same index rules\n @@ to_tsquery() : ✅ stemmed, language-aware, GIN-indexed\n

Enable JavaScript to run the live terminal and track your progress.