@@ match operator in PostgreSQL

Introduction

The @@ operator helps you quickly find rows where text matches a search query using full-text search.

You want to search for articles containing certain words.

You need to filter products by keywords in descriptions.

You want to find customer feedback mentioning specific topics.

You want to build a search feature that ranks results by relevance.

Syntax

PostgreSQL

tsvector_column @@ to_tsquery('search_query')

tsvector_column holds searchable text in a special format.

to_tsquery('search_query') converts your search words into a query format.

Examples

Find rows where both 'cat' and 'dog' appear.

PostgreSQL

content_vector @@ to_tsquery('cat & dog')

Find rows where either 'cat' or 'dog' appear.

PostgreSQL

content_vector @@ to_tsquery('cat | dog')

Find rows with words starting with 'cat', like 'cats' or 'category'.

PostgreSQL

content_vector @@ to_tsquery('cat:*')

Sample Program

This creates a table with articles, stores searchable text in content_vector, inserts three articles, then finds articles mentioning 'dog'.

PostgreSQL

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

INSERT INTO articles (title, body, content_vector) VALUES
('Cats and Dogs', 'Cats and dogs are common pets.', to_tsvector('Cats and dogs are common pets.')),
('Bird Watching', 'Birds are beautiful creatures.', to_tsvector('Birds are beautiful creatures.')),
('Dog Training', 'Training your dog is important.', to_tsvector('Training your dog is important.'));

SELECT id, title FROM articles WHERE content_vector @@ to_tsquery('dog');

Important Notes

Make sure to update the tsvector column when text changes.

The @@ operator is fast when you create an index on the tsvector column.

Summary

The @@ operator matches text columns against search queries.

Use to_tsquery() to build your search terms.

This helps build powerful text search features in PostgreSQL.