Full-Text SearchIntermediate
1Question 1 of 1
sqlHow does Full-Text Search work in PostgreSQL using tsvector and tsquery?
Explanation & Concept
PostgreSQL provides built-in search engine capabilities without needing external services like Elasticsearch for simple to medium workloads.
• `to_tsvector`: Parses a string into a list of normalized, lexically stemmed tokens (lexemes). • `to_tsquery`: Parses a search query with boolean operators (`&`, `|`, `!`, `<->` for proximity). • The `@@` operator matches a vector against a query.
Code Example
sql
-- Full text search with ranking
SELECT
title,
ts_rank(to_tsvector('english', content), query) AS relevance
FROM articles,
to_tsquery('english', 'database & (optimization | scaling)') query
WHERE to_tsvector('english', content) @@ query
ORDER BY relevance DESC
LIMIT 10;How It Works & Key Takeaways
Stemming ensures that searching for 'optimization' also matches 'optimizing' and 'optimized'. Pre-computing `tsvector` columns with GIN indexes makes searches sub-millisecond.
Mock Test · 1 Questions · Optional
Ready for Window Functions?
Take a quick test to evaluate yourself, or move straight to the next module.
Intermediate2 questions