@@ match operator in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to find rows where the column 'description' matches the text 'database'.

PostgreSQL

SELECT * FROM products WHERE description [1] 'database';

Drag options to blanks, or click blank then click option'

ALIKE

B=

C@@

DILIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using LIKE instead of @@ for full-text search.

Using = which checks exact equality, not full-text match.

The @@ operator is used in PostgreSQL to perform full-text search matching.

2fill in blank

medium

Complete the code to search the 'content' column for the phrase 'open source' using full-text search.

PostgreSQL

SELECT * FROM articles WHERE to_tsvector(content) [1] to_tsquery('open & source');

Drag options to blanks, or click blank then click option'

A=

B@@

CLIKE

DILIKE

Attempts:

3 left

💡 Hint

Common Mistakes

Using = which does not work for tsvector and tsquery.

Using LIKE which is for pattern matching, not full-text search.

The @@ operator matches the tsvector against the tsquery for full-text search.

3fill in blank

hard

Fix the error in the query to correctly match 'summary' column with the search term 'performance'.

PostgreSQL

SELECT * FROM reports WHERE summary @@ [1]('performance');

Drag options to blanks, or click blank then click option'

Ato_tsquery

Bto_tsvector

Cplainto_tsquery

Dtsvector

Attempts:

3 left

💡 Hint

Common Mistakes

Using to_tsvector which converts text to tsvector, not tsquery.

Using plainto_tsquery which is similar but less flexible.

to_tsquery converts the search term into a tsquery for matching with @@ operator.

4fill in blank

hard

Fill both blanks to create a query that matches 'notes' column against the phrase 'data analysis'.

PostgreSQL

SELECT * FROM logs WHERE [1](notes) [2] to_tsquery('data & analysis');

Drag options to blanks, or click blank then click option'

Ato_tsvector

Bto_tsquery

C@@

D=

Attempts:

3 left

💡 Hint

Common Mistakes

Using = instead of @@ for matching.

Using to_tsquery on the column instead of to_tsvector.

to_tsvector converts the column to tsvector, @@ matches it against the tsquery.

5fill in blank

hard

Fill all three blanks to write a query that selects 'id' and 'text' from 'messages' where 'text' matches 'urgent update'.

PostgreSQL

SELECT id, text FROM messages WHERE [1](text) [2] [3]('urgent & update');

Drag options to blanks, or click blank then click option'

Ato_tsvector

B@@

Cto_tsquery

Dplainto_tsquery

Attempts:

3 left

💡 Hint

Common Mistakes

Using plainto_tsquery which does not support boolean operators like &.

Using = instead of @@ for matching.

to_tsvector converts text, @@ matches it, to_tsquery converts the search phrase.

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