@@ match operator in PostgreSQL - Mini Project: Build & Apply

Using the @@ Match Operator in PostgreSQL Full-Text Search

📖 Scenario: You are building a simple search feature for a library database. Users want to find books by searching keywords in the book titles.

🎯 Goal: Create a table of books, add a full-text search configuration, and write a query using the @@ match operator to find books matching a search phrase.

📋 What You'll Learn

Create a table called books with columns id (integer) and title (text).

Insert exactly these three books: 'The Great Gatsby', 'Great Expectations', and 'The Grapes of Wrath'.

Create a tsvector column called title_vector that stores the full-text search document for the title column.

Write a query that uses the @@ operator to find books whose title_vector matches the search phrase 'great'.

💡 Why This Matters

🌍 Real World

Full-text search is used in many applications like library catalogs, blogs, and e-commerce sites to let users find relevant content quickly.

💼 Career

Understanding full-text search and the @@ operator is important for database developers and backend engineers working with search features.

1

Create the books table and insert data

Create a table called books with columns id as integer and title as text. Then insert these exact rows: (1, 'The Great Gatsby'), (2, 'Great Expectations'), and (3, 'The Grapes of Wrath').

PostgreSQL

-- Create the books table and insert the three rows
-- Your code here

Hint

Use CREATE TABLE to define the table and INSERT INTO to add the rows.

2

Add a title_vector column for full-text search

Add a new column called title_vector of type tsvector to the books table. Then update this column for all rows by converting the title column to a tsvector using to_tsvector('english', title).

PostgreSQL

ALTER TABLE books ADD COLUMN title_vector tsvector;
-- Update title_vector with to_tsvector for each title
-- Your code here

Hint

Use ALTER TABLE to add the column and UPDATE with to_tsvector to fill it.

3

Write a query using the @@ match operator

Write a SELECT query to get all columns from books where title_vector matches the search phrase 'great' using the @@ operator and to_tsquery('english', 'great').

PostgreSQL

-- Write a SELECT query using @@ to find books matching 'great'
-- Your code here

Hint

Use SELECT * FROM books WHERE title_vector @@ to_tsquery('english', 'great').

4

Complete the full-text search setup with an index

Create a GIN index on the title_vector column of the books table to speed up full-text search queries.

PostgreSQL

-- Create a GIN index on title_vector
-- Your code here

Hint

Use CREATE INDEX idx_title_vector ON books USING GIN(title_vector); to create the index.

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