to_tsquery for search terms in PostgreSQL - Mini Project: Build & Apply

Using to_tsquery for Full-Text Search in PostgreSQL

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

🎯 Goal: Create a PostgreSQL table to store book titles, then use to_tsquery to search for books matching specific search terms.

📋 What You'll Learn

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

Insert exactly three books with given titles.

Create a search query variable using to_tsquery with the term 'data & science'.

Write a SELECT query that uses to_tsquery to find books whose titles match the search query.

💡 Why This Matters

🌍 Real World

Full-text search is used in many applications like library catalogs, online stores, and document management systems to help users find relevant information quickly.

💼 Career

Knowing how to use PostgreSQL full-text search functions like to_tsquery and to_tsvector is valuable for database developers and backend engineers working on 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 three rows exactly: (1, 'Data Science Handbook'), (2, 'Introduction to Databases'), (3, 'Science and Technology').

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

Create a search query using to_tsquery

Create a variable called search_query that uses to_tsquery with the search term 'data & science'.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
  (1, 'Data Science Handbook'),
  (2, 'Introduction to Databases'),
  (3, 'Science and Technology');

-- Create search_query using to_tsquery with 'data & science'
-- Your code here

Need a hint?

Use to_tsquery('data & science') to create the search query.

3

Write a SELECT query using to_tsquery to search titles

Write a SELECT query that returns id and title from books where the title matches the to_tsquery('data & science') search query. Use the to_tsvector function on title for matching.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
  (1, 'Data Science Handbook'),
  (2, 'Introduction to Databases'),
  (3, 'Science and Technology');

-- Select id and title where title matches to_tsquery('data & science')
-- Your code here

Need a hint?

Use to_tsvector(title) @@ to_tsquery('data & science') in the WHERE clause to filter matching rows.

4

Complete the search query with an alias

Modify the SELECT query to add an alias search_query for to_tsquery('data & science') in the SELECT list, and keep the WHERE clause filtering with the same to_tsquery.

PostgreSQL

CREATE TABLE books (id INTEGER, title TEXT);
INSERT INTO books (id, title) VALUES
  (1, 'Data Science Handbook'),
  (2, 'Introduction to Databases'),
  (3, 'Science and Technology');

-- Add search_query alias in SELECT list
-- Your code here

Need a hint?

Add to_tsquery('data & science') AS search_query to the SELECT list.