to_tsquery for search terms in PostgreSQL

Introduction

to_tsquery helps you find words or phrases in text quickly by turning your search words into a special query format.

You want to search for specific words in a large text column.

You need to find documents containing certain keywords.

You want to combine words with AND, OR, or NOT to refine your search.

You want to ignore small words like 'the' or 'and' automatically.

You want to search using word stems (like 'run' to find 'running').

Syntax

PostgreSQL

to_tsquery('search_term')

The search_term can include words combined with & (AND), | (OR), and ! (NOT).

Words are stemmed automatically, so 'run' matches 'running' or 'runs'.

Examples

Search for rows containing both 'cat' and 'dog'.

PostgreSQL

SELECT to_tsquery('cat & dog');

Search for rows containing either 'cat' or 'dog'.

PostgreSQL

SELECT to_tsquery('cat | dog');

Search for rows containing 'cat' but not 'dog'.

PostgreSQL

SELECT to_tsquery('cat & !dog');

Search for rows containing words like 'run', 'running', or 'runs'.

PostgreSQL

SELECT to_tsquery('run');

Sample Program

This query creates a search for text containing 'coffee' but not 'tea'.

PostgreSQL

SELECT to_tsquery('coffee & !tea');

Important Notes

to_tsquery returns a special search query, not the search results themselves.

Use to_tsquery with the @@ operator to filter rows, like WHERE text_column @@ to_tsquery('word').

Search terms are case-insensitive and stemmed automatically.

Summary

to_tsquery converts search words into a format PostgreSQL can use to find text.

You can combine words with AND (&), OR (|), and NOT (!) operators.

It helps find words even if they appear in different forms (like 'run' and 'running').