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

Need a 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

Need a 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

Need a 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

Need a hint?

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