Beginner LevelPostgreSQL
How does native Full-Text Search work using to_tsvector and to_tsquery?
❓ Interview Prompt: Explain Full-Text Search, tokenization into lexemes (`to_tsvector`), parsing search queries (`to_tsquery`), `@@` operator, and `ts_rank`.
PostgreSQL includes native Full-Text Search without third-party search engines:
1
'to_tsvector('english', document)': Tokenizes text into normalized lexemes, removing stop words ('the', 'a', 'is') and stemming words ('running' $\rightarrow$ 'run').
2
'to_tsquery('english', 'search & term')': Parses search queries with logical AND ('&'), OR ('|'), and NOT ('!') operators.
3
'@@' Match Operator: Evaluates if 'tsvector' matches 'tsquery'.
Code Implementation Example
sql
SELECT title, ts_rank(to_tsvector('english', body), query) AS rank
FROM articles, to_tsquery('english', 'database & performance') query
WHERE to_tsvector('english', body) @@ query
ORDER BY rank DESC;💡Executing ranked full-text search using to_tsvector and to_tsquery.
🎯 Interview Key Takeaway
Store generated tsvector data in a generated column with a GIN index for high-speed full-text search.
#Full-Text Search#to_tsvector#to_tsquery#Beginner
Back to all PostgreSQL Questions→