Full-text search with PostgreSQL and tsvector

-- 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;