PostgreSQL Full-Text Search: A Comprehensive Guide
Learn how to implement full-text search in PostgreSQL using tsvector and tsquery. Optimize search queries with advanced indexing and configuration tips.
What is PostgreSQL Full-Text Search?
Full-text search in PostgreSQL enables querying text data with advanced linguistic matching, handling typos, stemming, and synonyms. It's a powerful tool for search-heavy applications like blogs, e-commerce, or forums..
1. Setup for Full-Text Search
To use full-text search, you need:
2. Example: Simple Full-Text Search
Consider a documents table:
id content 1 PostgreSQL is amazing! 2 Learn PostgreSQL search.Insert some data:
Code:
INSERT INTO documents (content)
VALUES ('PostgreSQL is amazing!'),
('Learn PostgreSQL search.');
Query using to_tsvector and to_tsquery:
Code:
SELECT *
FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('PostgreSQL');
This finds all rows containing the word "PostgreSQL".
3. Using Indexes for Performance
To improve query performance:
Code:
CREATE INDEX idx_content_tsvector
ON documents USING GIN (to_tsvector('english', content));
This index ensures faster searches on the content column.
4. Handling Stemming and Stop Words
PostgreSQL's full-text search automatically reduces words to their stems (e.g., "running" → "run") and ignores common words (e.g., "is", "the").
To customize this:
5. Search with Rankings
Order results based on relevance using ts_rank:
Code:
SELECT id, ts_rank(to_tsvector('english', content), to_tsquery('PostgreSQL')) AS rank
FROM documents
WHERE to_tsvector('english', content) @@ to_tsquery('PostgreSQL')
ORDER BY rank DESC;
6. Advanced Search: Phrases and Boolean Logic
You can use:
Best Practices
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4