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