@@ match operator in PostgreSQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the @@ match operator do in PostgreSQL?

The @@ operator is used to perform full-text search matching between a text search vector and a text search query. It returns true if the vector matches the query.

beginner

How do you create a text search vector to use with @@?

You can create a text search vector using the to_tsvector function, which converts text into a searchable document format for full-text search.

beginner

What function is commonly used to create the query part for the @@ operator?

The to_tsquery function is used to create a text search query that can be matched against a text search vector using @@.

intermediate

Example: What does this query do? SELECT * FROM articles WHERE to_tsvector('english', content) @@ to_tsquery('english', 'database & search');

It finds all articles where the content contains words matching both 'database' and 'search' according to English text search rules.

intermediate

Why is the @@ operator useful compared to simple LIKE searches?

Because @@ uses full-text search, it understands word forms, ignores stop words, and can match complex queries efficiently, unlike simple pattern matching with LIKE.

What does the @@ operator compare in PostgreSQL?

A Two plain text strings

B Two numeric values

C A text search vector and a text search query

D Two JSON objects

The @@ operator compares a text search vector with a text search query to check for matches.

Which function creates a text search vector for use with @@?

A ts_headline

B to_tsquery

C ts_rank

D to_tsvector

to_tsvector converts text into a searchable vector format for full-text search.

Which function creates the query part for the @@ operator?

A to_tsquery

B ts_rank

C plainto_tsquery

D to_tsvector

to_tsquery creates a text search query to match against a vector.

What does the query 'database & search' mean in to_tsquery?

A Match either 'database' or 'search'

B Match both 'database' and 'search'

C Match the phrase 'database search'

D Match words starting with 'data'

The & operator means both words must be present in the matched text.

Why is full-text search with @@ faster than LIKE for large text?

A Because it uses indexes optimized for text search

B Because LIKE uses parallel processing

C Because @@ ignores case sensitivity

D Because LIKE only works on numbers

Full-text search uses special indexes that speed up searching large text data.

Explain how the @@ operator works in PostgreSQL full-text search.

Think about how text is prepared and matched.

Describe the advantages of using @@ over simple LIKE for searching text.

Consider accuracy and performance.