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

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