-- Create table with tsvector column
CREATE TABLE articles (
id SERIAL PRIMARY KEY,
title VARCHAR(200),
content TEXT,
author VARCHAR(100),
published_at TIMESTAMP,
search_vector tsvector
);
-- Populate search vector (combines title and content)
UPDATE articles
SET search_vector =
setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
setweight(to_tsvector('english', COALESCE(content, '')), 'B');
-- Create GIN index for fast searching
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);
-- Basic search query
SELECT title, content
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & performance');
-- Search with OR
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'database | sql');
-- Search with NOT
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql & !mysql');
-- Phrase search (words in order)
SELECT title
FROM articles
WHERE search_vector @@ phraseto_tsquery('english', 'query optimization');
-- Plain text search (auto-converts to tsquery)
SELECT title
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'database performance tuning');
-- Web search syntax (PostgreSQL 11+)
SELECT title
FROM articles
WHERE search_vector @@ websearch_to_tsquery('english',
'"database optimization" -mysql OR postgresql');
-- Ranking results by relevance
SELECT
title,
ts_rank(search_vector, query) AS rank
FROM articles,
to_tsquery('english', 'postgresql & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;
-- Advanced ranking (weights title higher)
SELECT
title,
ts_rank_cd(search_vector, query, 32) AS rank
FROM articles,
to_tsquery('english', 'database') query
WHERE search_vector @@ query
ORDER BY rank DESC;
-- Highlight matches
SELECT
title,
ts_headline('english', content, query,
'StartSel=<mark>, StopSel=</mark>, MaxWords=50') AS snippet
FROM articles,
to_tsquery('english', 'postgresql') query
WHERE search_vector @@ query;
-- Auto-update search vector with trigger
CREATE OR REPLACE FUNCTION articles_search_trigger()
RETURNS TRIGGER AS $$
BEGIN
NEW.search_vector :=
setweight(to_tsvector('english', COALESCE(NEW.title, '')), 'A') ||
setweight(to_tsvector('english', COALESCE(NEW.content, '')), 'B') ||
setweight(to_tsvector('english', COALESCE(NEW.author, '')), 'C');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER tsvector_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION articles_search_trigger();
-- Generated column (PostgreSQL 12+)
ALTER TABLE articles
ADD COLUMN search_vector_generated tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
setweight(to_tsvector('english', COALESCE(content, '')), 'B')
) STORED;
CREATE INDEX idx_articles_generated
ON articles USING GIN(search_vector_generated);
-- Multi-language search
CREATE TABLE documents (
id SERIAL PRIMARY KEY,
title TEXT,
content TEXT,
language VARCHAR(10),
search_vector tsvector
);
-- Update with dynamic language
UPDATE documents
SET search_vector = to_tsvector(language::regconfig, content);
-- Search configuration details
SELECT * FROM pg_ts_config WHERE cfgname = 'english';
-- Available dictionaries
SELECT * FROM pg_ts_dict;
-- Parse text to see tokens
SELECT * FROM ts_debug('english',
'The quick brown fox jumps over the lazy dog');
-- Custom dictionary for synonyms
CREATE TEXT SEARCH DICTIONARY synonym_dict (
TEMPLATE = synonym,
SYNONYMS = my_synonyms
);
-- my_synonyms file:
-- db database
-- pg postgres postgresql
-- Prefix matching (autocomplete)
SELECT title
FROM articles
WHERE search_vector @@ to_tsquery('english', 'databa:*');
-- Matches: database, databases, etc.
-- Complex boolean search
SELECT title, ts_rank(search_vector, query) as rank
FROM articles,
to_tsquery('english',
'(postgresql | postgres) & (performance | optimization) & !mysql'
) query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
-- Search across multiple columns without tsvector
SELECT title
FROM articles
WHERE to_tsvector('english', title || ' ' || content)
@@ to_tsquery('english', 'search terms');
-- Better: Use index on expression
CREATE INDEX idx_articles_content_search
ON articles
USING GIN(to_tsvector('english', title || ' ' || content));
-- Fuzzy search with pg_trgm extension
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_articles_title_trgm
ON articles
USING GIN(title gin_trgm_ops);
-- Similarity search
SELECT title, similarity(title, 'databas performanc') as sim
FROM articles
WHERE title % 'databas performanc' -- % is similarity operator
ORDER BY sim DESC;
-- Combined full-text and fuzzy search
SELECT DISTINCT title
FROM articles
WHERE search_vector @@ plainto_tsquery('english', 'database')
OR title % 'database'
ORDER BY ts_rank(search_vector, plainto_tsquery('english', 'database')) DESC;
