How to Use to_tsquery in PostgreSQL for Full-Text Search

In PostgreSQL, to_tsquery converts a text search query string into a format that can be used to search text columns efficiently. You use it with full-text search functions like @@ to find rows matching the search terms. For example, to_tsquery('cat & dog') searches for rows containing both 'cat' and 'dog'.

Syntax

The to_tsquery function takes a text string representing the search query and returns a tsquery type used for full-text search. The query string can include logical operators like & (AND), | (OR), and ! (NOT).

• to_tsquery(query_text): Converts the query text into a searchable tsquery.
• query_text: A string with words and operators to define the search.

Example operators:

• cat & dog: Matches rows containing both 'cat' and 'dog'.
• cat | dog: Matches rows containing 'cat' or 'dog'.
• cat & !dog: Matches rows containing 'cat' but not 'dog'.

sql

SELECT to_tsquery('english', 'cat & dog');

Output

'cat' & 'dog'

Example

This example shows how to use to_tsquery with a table to find rows matching a full-text search query.

sql

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

INSERT INTO articles (content) VALUES
('The cat sat on the mat.'),
('Dogs are great pets.'),
('Cats and dogs can live together.');

-- Create a tsvector column for full-text search
ALTER TABLE articles ADD COLUMN content_vector tsvector;

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

-- Search for rows containing both 'cat' and 'dog'
SELECT id, content FROM articles
WHERE content_vector @@ to_tsquery('english', 'cat & dog');

Output

 id |               content                
----+------------------------------------
  3 | Cats and dogs can live together.
(1 row)

Common Pitfalls

Some common mistakes when using to_tsquery include:

• Not using the correct syntax for operators, causing errors or unexpected results.
• Passing plain text without operators, which may not match as expected.
• Confusing to_tsquery with plainto_tsquery, which automatically adds AND operators between words.
• Not normalizing text with to_tsvector before searching.

Example of wrong and right usage:

sql

-- Wrong: missing operator between words
SELECT to_tsquery('english', 'cat dog');

-- Right: use AND operator
SELECT to_tsquery('english', 'cat & dog');

Output

ERROR:  syntax error in tsquery: "cat dog"

 to_tsquery 
------------
 'cat' & 'dog'
(1 row)

Quick Reference

Operator	Meaning	Example
&	AND - both terms must be present	'cat & dog'
|	OR - either term can be present	'cat | dog'
!	NOT - term must not be present	'cat & !dog'
<->	FOLLOWED BY - term1 immediately before term2	'cat <-> dog'
*	Prefix matching - words starting with term	'cat*'

Key Takeaways

Use to_tsquery to convert search strings with logical operators into searchable queries.

Combine to_tsquery with @@ operator to filter rows matching full-text search conditions.

Always use proper operators like & (AND), | (OR), and ! (NOT) in your query string.

Remember to prepare your text data with to_tsvector before searching.

Avoid syntax errors by not omitting operators between search terms.