SQL
Implement Basic Full-Text Search with PostgreSQL
Learn to perform efficient full-text searches on textual data in PostgreSQL using `to_tsvector` and `to_tsquery` functions for advanced pattern matching.
-- First, create a GIN index for performance (optional but highly recommended for large tables):
-- CREATE INDEX idx_searchable_content ON articles USING GIN(to_tsvector('english', title || ' ' || content));
SELECT
id,
title,
content
FROM
articles
WHERE
to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', 'database & performance');
-- Example with phrase search (note the single quotes around the phrase search):
-- SELECT
-- id,
-- title,
-- content
-- FROM
-- articles
-- WHERE
-- to_tsvector('english', title || ' ' || content) @@ to_tsquery('english', '''web development''');
How it works: This PostgreSQL snippet demonstrates basic full-text search. It converts the `title` and `content` columns into a `tsvector` (a sorted list of unique lexemes). Then, it checks if this `tsvector` matches a `tsquery` (the search terms). The `@@` operator performs the match. An optional GIN index is recommended for performance on large datasets. This enables powerful keyword searches beyond simple `LIKE` comparisons, supporting operators like `&` (AND), `|` (OR), `!` (NOT), and phrase searches.