How to Use @@ Operator for Text Search in PostgreSQL

In PostgreSQL, the @@ operator is used to perform full-text search by matching a tsvector column against a tsquery. It returns true if the text matches the search query, enabling fast and flexible text searching.

Syntax

The @@ operator compares a tsvector (document) with a tsquery (search query). It returns true if the document matches the query.

• tsvector: A processed text column optimized for searching.
• tsquery: A search expression with keywords and operators.
• @@: The match operator that returns a boolean.

sql

tsvector_column @@ to_tsquery('search_query')

Example

This example shows how to create a table with a tsvector column, insert data, and use the @@ operator to find rows matching a search phrase.

sql

CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title TEXT,
  body TEXT,
  document_with_weights tsvector
);

-- Insert sample data
INSERT INTO articles (title, body) VALUES
('PostgreSQL Tutorial', 'Learn how to use PostgreSQL full-text search.'),
('Database Guide', 'This guide covers SQL and database basics.'),
('Text Search Tips', 'Tips for efficient text searching in PostgreSQL.');

-- Populate the tsvector column combining title and body
UPDATE articles SET document_with_weights =
  setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
  setweight(to_tsvector('english', coalesce(body, '')), 'B');

-- Query using @@ operator
SELECT id, title FROM articles
WHERE document_with_weights @@ to_tsquery('text & search');

Output

 id |       title        
----+--------------------
  3 | Text Search Tips    
(1 row)

Common Pitfalls

Common mistakes when using the @@ operator include:

• Not converting text to tsvector before searching.
• Using plain strings instead of to_tsquery() or plainto_tsquery() for the search query.
• Ignoring language configuration which affects stemming and stop words.
• Not updating the tsvector column after data changes.

Example of wrong and right usage:

sql

-- Wrong: comparing raw text with to_tsquery
SELECT * FROM articles WHERE body @@ to_tsquery('search');

-- Right: use tsvector column
SELECT * FROM articles WHERE document_with_weights @@ to_tsquery('search');

Quick Reference

Element	Description	Example
tsvector	Processed text for searching	to_tsvector('english', body)
tsquery	Search query expression	to_tsquery('text & search')
@@ operator	Matches tsvector against tsquery	document @@ to_tsquery('search')
setweight()	Assigns importance to parts of text	setweight(to_tsvector('english', title), 'A')
plainto_tsquery()	Converts plain text to tsquery	plainto_tsquery('simple search')

Key Takeaways

The @@ operator matches a tsvector column against a tsquery to perform full-text search.

Always convert your text data to tsvector format before using @@ for searching.

Use to_tsquery() or plainto_tsquery() to create search queries with @@ operator.

Keep your tsvector columns updated when the underlying text changes for accurate search results.

Language configuration affects how text is parsed and matched in full-text search.