How to Use tsquery in PostgreSQL for Full-Text Search

In PostgreSQL, tsquery is a data type used to represent text search queries for full-text search. You create a tsquery using functions like to_tsquery() or plainto_tsquery() and use it to search tsvector columns with the @@ operator.

Syntax

The tsquery type represents a text search query in PostgreSQL. You can create a tsquery using functions like to_tsquery('query') or plainto_tsquery('query'). The main operator to test if a tsvector matches a tsquery is @@.

• to_tsquery('query'): Parses the query string with logical operators like & (AND), | (OR), ! (NOT).
• plainto_tsquery('query'): Converts plain text into a query with AND between words.
• tsvector_column @@ tsquery: Returns true if the text vector matches the query.

sql

SELECT to_tsquery('fat & rat');
SELECT plainto_tsquery('fat rat');

-- Using tsquery to search a tsvector
SELECT 'fat rat cat'::tsvector @@ to_tsquery('fat & rat');

Output

to_tsquery
------------
'fat' & 'rat'

plainto_tsquery
----------------
'fat' & 'rat'

?column?
----------
 t
(1 row)

Example

This example shows how to create a table with a tsvector column, insert data, and use tsquery to search for matching rows.

sql

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

-- Insert sample data
INSERT INTO articles (content, content_vector) VALUES
('The fat rat sat on the mat.', to_tsvector('The fat rat sat on the mat.')),
('The quick brown fox jumps.', to_tsvector('The quick brown fox jumps.'));

-- Search for rows matching 'fat & rat'
SELECT id, content FROM articles WHERE content_vector @@ to_tsquery('fat & rat');

Output

id |           content           
----+-----------------------------
  1 | The fat rat sat on the mat.
(1 row)

Common Pitfalls

Common mistakes when using tsquery include:

• Using to_tsquery() with plain text without operators, which can cause errors.
• Not normalizing text with to_tsvector() before searching.
• Confusing plainto_tsquery() and to_tsquery()—the former treats input as plain words joined by AND, the latter expects operators.

Example of wrong and right usage:

sql

-- Wrong: to_tsquery with plain text (causes error)
-- SELECT to_tsquery('fat rat');

-- Right: use plainto_tsquery for plain text
SELECT plainto_tsquery('fat rat');

Output

plainto_tsquery
----------------
'fat' & 'rat'
(1 row)

Quick Reference

Function/Operator	Description
to_tsquery('query')	Creates a tsquery from a query string with operators (&, |, !)
plainto_tsquery('text')	Creates a tsquery from plain text, joining words with AND
to_tsvector('text')	Converts text to a tsvector for searching
tsvector_column @@ tsquery	Returns true if the tsvector matches the tsquery

Key Takeaways

Use to_tsquery() to create complex search queries with logical operators.

Use plainto_tsquery() for simple plain-text queries without operators.

Search tsvector columns using the @@ operator with a tsquery.

Always convert your searchable text to tsvector format before querying.

Avoid passing plain text directly to to_tsquery() without operators to prevent errors.