SQL
Implement Full-Text Search in PostgreSQL
Leverage PostgreSQL's powerful text search capabilities to perform advanced, language-aware full-text queries on documents and text fields for relevant search results.
-- 1. Create a GIN index for faster searches (recommended for larger tables):
-- CREATE INDEX idx_fts_documents ON documents USING GIN(to_tsvector('english', title || ' ' || body));
-- 2. Search for documents containing "web development" OR "programming"
SELECT id, title, body
FROM documents
WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', 'web & development | programming');
-- 3. Search for a phrase "relational database"
-- SELECT id, title, body
-- FROM documents
-- WHERE to_tsvector('english', title || ' ' || body) @@ to_tsquery('english', '"relational database"');
How it works: This snippet illustrates how to perform full-text searches using PostgreSQL's built-in text search functionality. It first creates a `tsvector` (a processed document for searching) from combined text fields (`title` and `body`) and then uses the `@@` operator with a `tsquery` to find matches. The example shows how to search for multiple keywords with OR (`|`) and AND (`&`) operators, providing a highly optimized and flexible way to query textual data.