âš¡IntermediateAdvanced Functions
Full-Text Search
Search text documents intelligently using to_tsvector, to_tsquery, and GIN indexes.
Overview
PostgreSQL's full-text search (FTS) goes beyond LIKE it tokenises text into lexemes (stemmed words), supports ranking, phrase matching, and Boolean queries. to_tsvector(text) converts a document to a searchable format; to_tsquery('word') creates a search query. @@ is the match operator. A GIN index on the tsvector makes FTS fast.
FTS understands language-specific stemming ('running' matches 'run'), stopwords ('the', 'is' are ignored), and ranking with ts_rank().
Key Points
- •to_tsvector('english', text) tokenise document
- •to_tsquery('english', 'word') parse search query
- •@@ match operator: tsvector @@ tsquery
- •GIN index: CREATE INDEX ON t USING GIN (to_tsvector('english', col))
- •ts_rank(tsvector, tsquery) relevance ranking
- •plainto_tsquery('two words') simple space-separated search
Examples
Full-text search
-- Create FTS index
CREATE INDEX idx_fts ON articles USING GIN (to_tsvector('english', body));
-- Search
SELECT title, ts_rank(to_tsvector('english', body), query) AS rank
FROM articles, to_tsquery('english', 'postgres & performance') query
WHERE to_tsvector('english', body) @@ query
ORDER BY rank DESC;
-- Highlight matches
SELECT ts_headline('english', body, to_tsquery('postgres'))
FROM articles WHERE to_tsvector('english', body) @@ to_tsquery('postgres');Exercises
Write SQL to solve each exercise. Your progress is saved automatically.
Loading editor…
Completed: 0 / 5 exercises
Knowledge Check
Question 1 of 4