PostgreSQL Full-Text Search

Full-Text Search (FTS) in PostgreSQL allows searching natural-language text efficiently. It supports indexing, ranking, and querying textual content with complex search patterns.

1. Key Concepts

• tsvector: A data type storing a processed document suitable for text search.
• tsquery: A query type used to search tsvector documents.
• to_tsvector(): Converts plain text into a tsvector for indexing.
• to_tsquery(): Converts a search string into a tsquery for searching.

2. Creating a Table for Full-Text Search

CREATE TABLE articles (
                id SERIAL PRIMARY KEY,
                title TEXT,
                content TEXT
            );

3. Inserting Data

INSERT INTO articles(title, content)
            VALUES
            ('PostgreSQL Tutorial', 'Learn PostgreSQL with examples for beginners and advanced users.'),
            ('Full-Text Search', 'PostgreSQL provides powerful full-text search capabilities.');

4. Creating tsvector Column and Index

-- Add tsvector column
            ALTER TABLE articles ADD COLUMN tsv_content tsvector;

            -- Update tsvector column with content
            UPDATE articles SET tsv_content = to_tsvector('english', content);

            -- Create GIN index for fast search
            CREATE INDEX idx_articles_tsv_content ON articles USING GIN(tsv_content);

5. Simple Full-Text Search

-- Search for the term 'PostgreSQL'
            SELECT title, content
            FROM articles
            WHERE tsv_content @@ to_tsquery('english', 'PostgreSQL');

6. Ranking Search Results

Use ts_rank() to rank results based on relevance:

SELECT title, content, ts_rank(tsv_content, to_tsquery('english', 'PostgreSQL')) AS rank
            FROM articles
            WHERE tsv_content @@ to_tsquery('english', 'PostgreSQL')
            ORDER BY rank DESC;

7. Advanced Queries

-- Search multiple terms
            SELECT title, content
            FROM articles
            WHERE tsv_content @@ to_tsquery('english', 'PostgreSQL & tutorial');

            -- Phrase search using plainto_tsquery
            SELECT title, content
            FROM articles
            WHERE tsv_content @@ plainto_tsquery('english', 'full text search');

8. Updating tsvector Column Automatically

Use triggers to update tsvector whenever content changes:

-- Create trigger function
            CREATE FUNCTION update_tsv_content() RETURNS trigger AS $$
            BEGIN
            NEW.tsv_content := to_tsvector('english', NEW.content);
            RETURN NEW;
            END;
            $$ LANGUAGE plpgsql;

            -- Attach trigger
            CREATE TRIGGER tsvector_update BEFORE INSERT OR UPDATE
            ON articles
            FOR EACH ROW EXECUTE FUNCTION update_tsv_content();

9. Best Practices

• Use tsvector columns with GIN indexes for high-performance search.
• Use triggers to keep tsvector columns updated automatically.
• Use plainto_tsquery() for user-friendly search inputs.
• Rank results with ts_rank() to provide relevance-based ordering.
• Combine FTS with filtering and pagination for production applications.

Conclusion

PostgreSQL Full-Text Search is a powerful feature for building search functionality into applications. With tsvector, tsquery, GIN indexes, and ranking functions, you can efficiently search large volumes of textual data. In the next tutorial, we will explore PostgreSQL Security and Roles for managing user access and permissions.