@@ 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.

Practice

1.

What does the @@ operator do in PostgreSQL?

easy

A. It concatenates two text strings.

B. It compares two numeric values for equality.

C. It matches a text column against a full-text search query.

D. It checks if a value is NULL.

Solution

1. Step 1: Understand the purpose of @@ operator

   The @@ operator is used in PostgreSQL for full-text search to match text columns against search queries.

2. Step 2: Compare with other options

   Other options describe unrelated operations like numeric comparison, string concatenation, or NULL checks, which are not related to @@.

3. Final Answer:

   It matches a text column against a full-text search query. -> Option C

4. Quick Check:

   @@ = full-text match [OK]

Hint: Remember: @@ is for full-text search matching [OK]

Common Mistakes:

• Confusing @@ with equality operator =
• Thinking @@ concatenates strings
• Assuming @@ checks for NULL

2.

Which of the following is the correct syntax to use the @@ operator with to_tsquery in PostgreSQL?

SELECT * FROM articles WHERE content @@ to_tsquery('search_term');

easy

A. SELECT * FROM articles WHERE content @@ to_tsquery('search_term');

B. SELECT * FROM articles WHERE content = to_tsquery('search_term');

C. SELECT * FROM articles WHERE content @@ 'search_term';

D. SELECT * FROM articles WHERE content LIKE to_tsquery('search_term');

Solution

1. Step 1: Identify correct usage of @@ with to_tsquery

   The @@ operator requires the right side to be a tsquery, which is created by to_tsquery().

2. Step 2: Check each option

   SELECT * FROM articles WHERE content @@ to_tsquery('search_term'); correctly uses content @@ to_tsquery('search_term'). Options B, C, and D misuse operators or omit to_tsquery().

3. Final Answer:

   SELECT * FROM articles WHERE content @@ to_tsquery('search_term'); -> Option A

4. Quick Check:

   Use @@ to_tsquery() syntax [OK]

Hint: Always use to_tsquery() on right side of @@ [OK]

Common Mistakes:

• Using = instead of @@
• Passing plain string without to_tsquery()
• Using LIKE operator for full-text search

3.

Given the table documents with a text_content column, what will this query return?

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

Assuming the table has these rows:

• 1: 'The cat chased the dog.'
• 2: 'A dog and a cat are friends.'
• 3: 'The cat is sleeping.'

medium

A. []

B. [1, 2]

C. [3]

D. [1, 2, 3]

Solution

1. Step 1: Understand the query condition

   The query uses to_tsquery('cat & dog'), which means both 'cat' AND 'dog' must be present in text_content.

2. Step 2: Check each row for both words

   Row 1 has 'cat' and 'dog'. Row 2 has both words as well. Row 3 only has 'cat', missing 'dog'.

3. Final Answer:

   [1, 2] -> Option B

4. Quick Check:

   AND search returns rows with both terms [OK]

Hint: Use & in to_tsquery for AND condition [OK]

Common Mistakes:

• Assuming OR instead of AND with &
• Expecting rows missing one word to match
• Confusing to_tsquery syntax

4.

Identify the error in this query:

SELECT * FROM posts WHERE content @@ 'cat | dog';

medium

A. content column cannot be used with @@ operator.

B. Incorrect use of the | operator inside the string.

C. The query should use LIKE instead of @@.

D. Missing to_tsquery() function around the search string.

Solution

1. Step 1: Check the right side of @@ operator

   The right side must be a tsquery type, usually created by to_tsquery(). Here, it's a plain string.

2. Step 2: Identify the missing function

   The query should be content @@ to_tsquery('cat | dog') to be valid.

3. Final Answer:

   Missing to_tsquery() function around the search string. -> Option D

4. Quick Check:

   Right side of @@ needs to_tsquery() [OK]

Hint: Always wrap search terms with to_tsquery() [OK]

Common Mistakes:

• Using plain string without to_tsquery()
• Thinking | operator is invalid inside to_tsquery
• Confusing @@ with LIKE

5.

You want to find all rows in messages where the body contains either 'error' or 'failure' but not both. Which to_tsquery expression with @@ operator will achieve this?

hard

A. body @@ to_tsquery('(error | failure) & !(error & failure)')

B. body @@ to_tsquery('error | failure & !error & !failure')

C. body @@ to_tsquery('error <-> failure')

D. body @@ to_tsquery('error | failure')

Solution

1. Step 1: Understand the requirement

   We want rows containing 'error' or 'failure' but NOT both. This is an exclusive OR (XOR) condition.

2. Step 2: Analyze the options

   body @@ to_tsquery('(error | failure) & !(error & failure)') uses (error | failure) & !(error & failure), which means 'error or failure' AND NOT 'both error and failure'. This matches XOR logic.

3. Step 3: Check other options

   body @@ to_tsquery('error | failure & !error & !failure') is invalid syntax. body @@ to_tsquery('error | failure') matches any with 'error' or 'failure' including both. body @@ to_tsquery('error <-> failure') looks for 'error' immediately followed by 'failure'.

4. Final Answer:

   body @@ to_tsquery('(error | failure) & !(error & failure)') -> Option A

5. Quick Check:

   XOR = (A OR B) AND NOT (A AND B) [OK]

Hint: Use (A | B) & !(A & B) for XOR in to_tsquery [OK]

Common Mistakes:

• Using simple OR instead of XOR
• Incorrect syntax for NOT operator
• Confusing proximity operator <-> with XOR