PostgreSQL Full-Text Search

Text Search Types

tsvector: Document representation (normalized tokens)
tsquery: Search query (boolean expression)

Basic Full-Text Search

from sqlalchemy import func, text

# Simple search
stmt = select(Article).where(
    func.to_tsvector('english', Article.content).match('python programming')
)

# Using @@ operator directly
stmt = select(Article).where(
    text("to_tsvector('english', content) @@ to_tsquery('english', 'python & programming')")
)

Stored tsvector Column

from sqlalchemy.dialects.postgresql import TSVECTOR

class Article(Base):
    __tablename__ = "articles"

    id: Mapped[int] = mapped_column(primary_key=True)
    title: Mapped[str] = mapped_column(String(200))
    content: Mapped[str] = mapped_column(Text)
    search_vector: Mapped[str] = mapped_column(TSVECTOR)

    __table_args__ = (
        Index('ix_article_search', 'search_vector', postgresql_using='gin'),
    )

# Create trigger to auto-update search_vector
"""
CREATE TRIGGER article_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);
"""

Search Queries

# AND search (both terms required)
query = "python & programming"

# OR search (either term)
query = "python | java"

# NOT search (exclude term)
query = "python & !java"

# Phrase search (adjacent words)
query = "python <-> programming"  # "python programming"

# Prefix search
query = "prog:*"  # Words starting with "prog"

Ranking Results

# ts_rank - relevance score
stmt = (
    select(
        Article,
        func.ts_rank(
            Article.search_vector,
            func.to_tsquery('english', 'python')
        ).label('rank')
    )
    .where(Article.search_vector.match('python'))
    .order_by(text('rank DESC'))
)

# ts_rank_cd - cover density ranking
stmt = select(
    Article,
    func.ts_rank_cd(Article.search_vector, func.to_tsquery('python'))
).order_by(text('2 DESC'))

# Headline (highlighted snippets)
stmt = select(
    Article.title,
    func.ts_headline(
        'english',
        Article.content,
        func.to_tsquery('english', 'python'),
        'StartSel=, StopSel='
    )
)