Full_text_search

Chapter 50: Full-Text Search

Searching Text Data

---

50.1 Understanding Full-Text Search

Full-text search allows you to search text data efficiently without scanning every row.

                    Full-Text Search Components
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                      FTS Components                                   │
    │                                                                      │
    │   Text Search Configuration:                                         │
    │   ─────────────────────────                                         │
    │   • Defines how text is processed                                    │
    │   • Includes dictionary and tokenizer                               │
    │   • english, simple, etc.                                         │
    │                                                                      │
    │   tsvector:                                                         │
    │   ─────────                                                         │
    │   • Preprocessed indexed form of text                               │
    │   • Lists lexemes with positions                                    │
    │                                                                      │
    │   tsquery:                                                          │
    │   ─────────                                                         │
    │   • Query in optimized form                                         │
    │   • Supports boolean operators                                     │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

50.2 Basic Full-Text Search

-- Enable extension (usually pre-installed)
CREATE EXTENSION IF NOT EXISTS unaccent;

-- Create table for search
CREATE TABLE articles (
    article_id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    search_vector TSVECTOR
);

-- Generate tsvector from columns
UPDATE articles
SET search_vector =
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', content), 'B');

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

-- Search using full-text search
SELECT title, ts_rank_cd(search_vector, query) as rank
FROM articles, to_tsquery('english', 'postgresql & tutorial') query
WHERE search_vector @@ query
ORDER BY rank DESC;

---

50.3 Text Search Functions

-- Convert text to tsvector
SELECT to_tsvector('english', 'PostgreSQL is a powerful database');

-- Create tsquery
SELECT to_tsquery('english', 'postgresql & tutorial');

-- Search with @@
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database');

-- Multiple words (any)
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'postgresql | mysql');

-- Phrase search
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database <-> tutorial');

-- Ranking results
SELECT title, ts_rank_cd(search_vector, query) as rank
FROM articles, to_tsquery('english', 'tutorial') query
WHERE search_vector @@ query
ORDER BY rank DESC;

---

50.4 Text Search Configuration

-- List available configurations
SELECT cfgname FROM pg_ts_config;

-- Use custom configuration
SELECT to_tsvector('simple', 'PostgreSQL Tutorial');

-- Create custom text search configuration
CREATE TEXT SEARCH CONFIGURATION my_config (COPY = english);

-- Add dictionary to configuration
ALTER TEXT SEARCH CONFIGURATION my_config
    ADD MAPPING FOR word WITH english_stem;

-- Use custom configuration
SELECT to_tsvector('my_config', 'PostgreSQL Tutorial');

---

50.5 Practical Examples

-- Search with highlighting
SELECT
    title,
    ts_headline('english', content, to_tsquery('english', 'tutorial'),
    ts_rank_cd(search_vector, query) as rank
FROM articles, to_tsquery('english', 'tutorial') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- Combined search
SELECT * FROM articles
WHERE
    to_tsvector('english', title || ' ' || content)
    @@ to_tsquery('english', 'database & tutorial');

-- Phrase search with FTS
SELECT * FROM articles
WHERE search_vector @@
    phraseto_tsquery('english', 'full text search');

---

Summary

Function	Purpose
to_tsvector	Create searchable index
to_tsquery	Create query
@@	Match operator
ts_rank	Rank results

---

This completes the PostgreSQL Guide!