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').

Practice

1. What does the to_tsquery function do in PostgreSQL?

easy

A. It converts search terms into a format for full-text search queries.

B. It creates a new table for storing search terms.

C. It updates text columns with search keywords.

D. It deletes rows that match search terms.

Solution

1. Step 1: Understand the purpose of to_tsquery

   to_tsquery converts plain search words into a special query format that PostgreSQL can use for full-text searching.

2. Step 2: Compare options with the function's role

   Only It converts search terms into a format for full-text search queries. correctly describes this conversion role. Other options describe unrelated database actions.

3. Final Answer:

   It converts search terms into a format for full-text search queries. -> Option A

4. Quick Check:

   to_tsquery = converts search terms [OK]

Hint: Remember: to_tsquery prepares search words for text search [OK]

Common Mistakes:

• Thinking it creates or modifies tables
• Confusing it with data update or delete operations
• Assuming it returns raw text instead of a query format

2. Which of the following is the correct syntax to create a to_tsquery for searching the words 'cat' AND 'dog' using the explicit AND operator?

easy

A. to_tsquery('cat | dog')

B. to_tsquery('cat & dog')

C. to_tsquery('cat dog')

D. to_tsquery('cat && dog')

Solution

1. Step 1: Recall the AND operator in to_tsquery

   The AND operator is represented by & in to_tsquery syntax.

2. Step 2: Match the correct syntax

   to_tsquery('cat & dog') uses & correctly between 'cat' and 'dog'. to_tsquery('cat | dog') uses OR, C misses operator, and D uses invalid &&.

3. Final Answer:

   to_tsquery('cat & dog') -> Option B

4. Quick Check:

   AND = & in to_tsquery [OK]

Hint: Use & for AND, | for OR in to_tsquery strings [OK]

Common Mistakes:

• Using spaces without operators to mean AND
• Using double ampersands (&&) instead of single &
• Confusing OR (|) with AND (&)

3. Given the query:
SELECT to_tsquery('fast & !slow');
What is the expected output?

medium

A. 'fast' | !'slow'

B. Syntax error

C. 'fast' & !'slow'

D. 'fast slow'

Solution

1. Step 1: Understand the operators in the query

   The query uses AND (&) between 'fast' and NOT (!) 'slow'. This means search for 'fast' but exclude 'slow'.

2. Step 2: Check the output format of to_tsquery

   PostgreSQL returns the query as a string with words quoted and operators preserved. So output is 'fast' & !'slow'.

3. Final Answer:

   'fast' & !'slow' -> Option C

4. Quick Check:

   AND = & and NOT = ! in output [OK]

Hint: NOT is !, AND is & in to_tsquery output [OK]

Common Mistakes:

• Confusing OR (|) with AND (&)
• Expecting raw words without quotes
• Thinking the query causes syntax error

4. Identify the error in this query:
SELECT to_tsquery('apple & | orange');

medium

A. Syntax error due to consecutive operators & |

B. No error, valid query

C. Error because words must be uppercase

D. Error because to_tsquery needs two arguments

Solution

1. Step 1: Analyze the operator usage in the query string

   The string has & | operators consecutively without a word between them, which is invalid syntax.

2. Step 2: Confirm PostgreSQL behavior

   PostgreSQL will raise a syntax error because operators must separate valid search terms, not each other.

3. Final Answer:

   Syntax error due to consecutive operators & | -> Option A

4. Quick Check:

   Operators must separate words, not each other [OK]

Hint: Avoid placing operators consecutively without words [OK]

Common Mistakes:

• Assuming multiple operators in a row are allowed
• Thinking case of words matters
• Believing to_tsquery requires multiple arguments

5. You want to search for documents containing the word 'run' but not 'running'. Which to_tsquery expression correctly achieves this?

hard

A. to_tsquery('run ! running')

B. to_tsquery('run | !running')

C. to_tsquery('run & !run')

D. to_tsquery('run & !running')

Solution

1. Step 1: Understand the requirement

   You want documents that contain 'run' but exclude those that contain 'running'. This means AND 'run' and NOT 'running'.

2. Step 2: Choose the correct operator usage

   AND is & and NOT is !. to_tsquery('run & !running') uses these correctly. to_tsquery('run | !running') uses OR which is wrong. to_tsquery('run & !run') excludes 'run' itself. to_tsquery('run ! running') has invalid syntax (missing operator between 'run' and '!').

3. Final Answer:

   to_tsquery('run & !running') -> Option D

4. Quick Check:

   AND = & and NOT = ! for correct filtering [OK]

Hint: Use & for AND, ! for NOT to exclude terms [OK]

Common Mistakes:

• Using OR instead of AND for inclusion
• Excluding the main word accidentally
• Missing operators between words