SQL
Advanced Full-Text Search with PostgreSQL `tsvector` and `tsquery`
Implement robust full-text search capabilities in PostgreSQL, leveraging `to_tsvector` and `to_tsquery` for efficient and relevant search results.
SELECT id, title, content
FROM documents
WHERE to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'search & term');
How it works: This snippet shows how to perform an advanced full-text search in PostgreSQL, which is far more powerful and performant than basic `LIKE` queries. It converts the `title` and `content` fields into a `tsvector` (a sorted list of unique lexemes) and then uses the `@@` operator to match it against a `to_tsquery` (a query represented by search terms and operators like `&` for AND, `|` for OR). This approach enables efficient and linguistically aware searching.