Highlighting with ts_headline in PostgreSQL - Mini Project: Build & Apply

Highlighting Search Terms with ts_headline in PostgreSQL

📖 Scenario: You are building a simple search feature for a book database. When users search for a word, you want to show the book titles with the searched word highlighted so it stands out.

🎯 Goal: Create a table with book titles, set up a search query, and use ts_headline to highlight the searched word in the titles.

📋 What You'll Learn

Create a table called books with a column title containing exact book titles.

Create a search query using to_tsquery with the exact search term 'adventure'.

Use ts_headline to highlight the word adventure in the book titles.

Return the original title and the highlighted title in the query result.

💡 Why This Matters

🌍 Real World

Highlighting search terms helps users quickly find relevant information in search results, improving user experience in apps like online bookstores or document search.

💼 Career

Knowing how to use full-text search and highlighting in PostgreSQL is valuable for backend developers and data engineers working on search features.

1

Create the books table and insert data

Create a table called books with a column title of type text. Insert these exact book titles: 'The Great Adventure', 'Adventure in the Mountains', 'Cooking Basics', 'Mystery of the Old House'.

PostgreSQL

-- Create table books and insert titles
-- Your code here

Need a hint?

Use CREATE TABLE to make the table and INSERT INTO to add the exact titles.

2

Create a search query variable

Create a variable called search_query and set it to to_tsquery('adventure') to represent the search term.

PostgreSQL

CREATE TABLE books (title text);
INSERT INTO books (title) VALUES
('The Great Adventure'),
('Adventure in the Mountains'),
('Cooking Basics'),
('Mystery of the Old House');

-- Create search_query variable
-- Your code here

Need a hint?

Use to_tsquery('adventure') to create the search query.

3

Use ts_headline to highlight the search term

Write a SELECT query that returns the original title and a new column called highlighted_title which uses ts_headline('english', title, to_tsquery('adventure')) to highlight the word adventure in the titles.

PostgreSQL

CREATE TABLE books (title text);
INSERT INTO books (title) VALUES
('The Great Adventure'),
('Adventure in the Mountains'),
('Cooking Basics'),
('Mystery of the Old House');

-- Select title and highlighted_title using ts_headline
-- Your code here

Need a hint?

Use ts_headline with the english configuration, the title column, and the search query to_tsquery('adventure').

4

Complete the query with a WHERE clause to filter results

Add a WHERE clause to the SELECT query to only show rows where the title matches the search query using to_tsvector('english', title) @@ to_tsquery('adventure').

PostgreSQL

CREATE TABLE books (title text);
INSERT INTO books (title) VALUES
('The Great Adventure'),
('Adventure in the Mountains'),
('Cooking Basics'),
('Mystery of the Old House');

-- Add WHERE clause to filter titles matching 'adventure'
-- Your code here

Need a hint?

Use WHERE to_tsvector('english', title) @@ to_tsquery('adventure') to filter titles containing the word.