@@ match operator in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Basic usage of @@ match operator

Given a table documents with a content column of type tsvector, what is the result of this query?

SELECT id FROM documents WHERE content @@ to_tsquery('cat & dog');

Assume the table has these rows:
1: 'cat dog mouse'
2: 'cat mouse'
3: 'dog cat'
4: 'dog elephant'

A [1, 3]

B [3, 4]

C [2, 4]

D [1, 2, 3]

💡 Hint

The @@ operator checks if the tsvector matches the tsquery condition.

The query looks for rows where the content contains both 'cat' AND 'dog'. Rows 1 and 3 have both words, so their ids are returned.

❓ query_result

intermediate

Using @@ with OR condition in tsquery

What rows will this query return?

SELECT id FROM documents WHERE content @@ to_tsquery('cat | elephant');

Given the same documents table as before with rows:
1: 'cat dog mouse'
2: 'cat mouse'
3: 'dog cat'
4: 'dog elephant'

A [1, 2, 4]

B [1, 2, 3]

C [2, 4]

D [1, 2, 3, 4]

💡 Hint

The | operator means OR in tsquery.

The query matches rows containing either 'cat' or 'elephant'. Rows 1, 2, 3, and 4 match because they contain 'cat' or 'elephant'.

📝 Syntax

advanced

Identify the syntax error in tsquery expression

Which option contains a syntax error in the tsquery passed to the @@ operator?

PostgreSQL

SELECT id FROM documents WHERE content @@ to_tsquery(?);

A 'cat | (dog & mouse)'

B 'cat & dog'

C 'cat & | dog'

D 'cat <-> dog'

💡 Hint

Look for invalid operator placement in the tsquery string.

Option C has an invalid operator sequence '& |' which is not allowed in tsquery syntax.

❓ optimization

advanced

Improving performance of @@ queries

Which option best improves performance of queries using the @@ operator on a large documents table with a content tsvector column?

A Use LIKE '%cat%' instead of @@

B Create a GIN index on the content column

C Add a B-tree index on the content column

D Convert content to text and index it

💡 Hint

Think about which index type supports full text search efficiently.

GIN indexes are designed for fast full text search with tsvector columns and @@ operator.

🧠 Conceptual

expert

Understanding @@ operator behavior with phrase search

Consider this query:

SELECT id FROM documents WHERE content @@ phraseto_tsquery('cat dog');

How does the @@ operator behave differently here compared to using to_tsquery('cat & dog')?

A It matches rows where 'cat' and 'dog' appear next to each other in order

B It matches rows where 'cat' or 'dog' appear anywhere in any order

C It matches rows where 'cat' and 'dog' appear anywhere but not necessarily adjacent

D It matches rows only if 'cat' appears before 'dog' anywhere in the text

💡 Hint

phraseto_tsquery creates a phrase search condition.

phraseto_tsquery creates a tsquery that matches the exact phrase with words adjacent and in order, unlike to_tsquery which matches words anywhere.