SQL
Implement Full-Text Search in SQL
Learn to implement powerful full-text search capabilities in your SQL database for efficient keyword-based querying on text columns using PostgreSQL.
-- PostgreSQL Full-Text Search Example
-- 1. Add a tsvector column and create a GIN index for performance
ALTER TABLE products ADD COLUMN textsearchable_index_col tsvector;
UPDATE products SET textsearchable_index_col = to_tsvector('english', name || ' ' || description);
CREATE INDEX products_textsearch_idx ON products USING GIN (textsearchable_index_col);
-- 2. Create a trigger to automatically update the tsvector column on insert/update
CREATE FUNCTION products_tsvector_trigger() RETURNS trigger AS $$
BEGIN
NEW.textsearchable_index_col := to_tsvector('english', NEW.name || ' ' || NEW.description);
RETURN NEW;
END
$$ LANGUAGE plpgsql;
CREATE TRIGGER products_tsvector_update BEFORE INSERT OR UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION products_tsvector_trigger();
-- 3. Perform a search query
SELECT id, name, description
FROM products
WHERE textsearchable_index_col @@ to_tsquery('english', 'laptop & powerful');
-- 4. Search with highlighting (optional)
SELECT id, name, description,
ts_headline('english', description, to_tsquery('english', 'laptop & powerful')) AS highlighted_description
FROM products
WHERE textsearchable_index_col @@ to_tsquery('english', 'laptop & powerful');
How it works: This snippet demonstrates how to set up and use full-text search in PostgreSQL. It involves adding a `tsvector` column derived from your text data, indexing it with a GIN index for speed, and maintaining it automatically with a trigger. Queries are performed using `to_tsquery` and the `@@` operator, optionally with `ts_headline` for result highlighting.