Text Search Basics in PostgreSQL

-- Create text search column
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- Update search vector
UPDATE articles
SET search_vector = to_tsvector('english', 
  title || ' ' || content);

-- Create GIN index
CREATE INDEX idx_search ON articles USING gin(search_vector);

-- Search with tsquery
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;

-- DETAILED_TAB:
-- Auto-update search vector with trigger
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS trigger AS $$
BEGIN
  NEW.search_vector := to_tsvector('english',
    COALESCE(NEW.title, '') || ' ' || 
    COALESCE(NEW.content, ''));
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trig_update_search
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();

-- Advanced search queries
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english',
  'postgresql & (index | performance) & !slow');

-- Highlight search results
SELECT ts_headline('english', content, query)
FROM articles, to_tsquery('english', 'database') query
WHERE search_vector @@ query;