@@ match operator in PostgreSQL - Step-by-Step Execution

Concept Flow - @@ match operator

Input: Text Column, Query String

↓

@@ Operator: Full Text Search Match

↓

Evaluate: Does Text Match Query?

↓

Return TRUE

The @@ operator checks if a text column matches a full-text search query, returning true or false.

Execution Sample

PostgreSQL

SELECT title FROM articles WHERE content @@ to_tsquery('database & search');

This query returns titles of articles whose content matches the full-text search query 'database & search'.

Execution Table

Step	Input Content	Query	@@ Evaluation	Result
1	'Learn about database search techniques'	'database & search'	TRUE	Row included
2	'Introduction to cooking recipes'	'database & search'	FALSE	Row excluded
3	'Advanced database search indexing methods'	'database & search'	TRUE	Row included
4	'Gardening tips for beginners'	'database & search'	FALSE	Row excluded
Exit	No more rows			Query ends

💡 All rows evaluated; query returns only rows where @@ operator is TRUE

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	Final
content		Learn about database search techniques	Introduction to cooking recipes	Advanced database search indexing methods	Gardening tips for beginners
query		database & search	database & search	database & search	database & search
@@ evaluation		TRUE	FALSE	TRUE	FALSE

Key Moments - 2 Insights

Why does the @@ operator return FALSE for some rows even if they contain one of the query words?

The @@ operator requires all parts of the query (like 'database' AND 'search') to be present in the text for TRUE. Partial matches do not satisfy the full query, as shown in rows 2 and 4 in the execution_table.

What does the to_tsquery function do in the query?

to_tsquery converts the plain text query string into a format that the @@ operator can use for full-text search matching, as seen in the execution_sample code.

Visual Quiz - 3 Questions

Look at the execution_table, what is the @@ evaluation result for the content 'Advanced database indexing methods'?

A FALSE

B TRUE

C NULL

D Error

💡 Hint

Check the third row under the '@@ Evaluation' column in the execution_table.

At which step does the @@ operator return FALSE?

A Step 1

B Step 2

C Step 3

D Step 4

💡 Hint

Look at the '@@ Evaluation' column for steps where the result is FALSE.

If the query was changed to 'database | search', how would the @@ evaluation change for 'Introduction to cooking recipes'?

A It would become TRUE

B It would remain FALSE

C It would cause an error

D It would become NULL

💡 Hint

Consider that 'Introduction to cooking recipes' contains neither 'database' nor 'search' words.

Concept Snapshot

PostgreSQL @@ operator checks if a text column matches a full-text search query.
Syntax: column @@ to_tsquery('query')
Returns TRUE if the text matches the query, FALSE otherwise.
Use to_tsquery to format the search query.
Supports logical operators like & (AND), | (OR).
Useful for efficient text searching in large datasets.

Full Transcript

The @@ operator in PostgreSQL is used for full-text search. It compares a text column against a search query formatted by to_tsquery. The operator returns TRUE if the text matches the query and FALSE if it does not. For example, if the query is 'database & search', the text must contain both words to return TRUE. The execution table shows step-by-step how different text inputs are evaluated against the query. Variables like content and query change as each row is checked. Common confusions include understanding that all parts of the query must match for TRUE and the role of to_tsquery in formatting the query. The visual quiz tests understanding of these points by asking about specific steps and hypothetical changes.

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