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

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

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

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

Hint

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

Practice

1. What is the main purpose of the ts_headline function in PostgreSQL?

easy

A. To remove stop words from a text

B. To highlight search terms in a text for easier identification

C. To convert text to lowercase

D. To count the number of words in a text

Solution

1. Step 1: Understand the function's role

   ts_headline is designed to highlight parts of text that match a search query.

2. Step 2: Compare options with function purpose

   Only To highlight search terms in a text for easier identification describes highlighting search terms, which matches ts_headline's purpose.

3. Final Answer:

   To highlight search terms in a text for easier identification -> Option B

4. Quick Check:

   Highlighting = D [OK]

Hint: Remember: ts_headline highlights matched words in text [OK]

Common Mistakes:

• Confusing ts_headline with text transformation functions
• Thinking it counts words or changes case
• Assuming it removes words instead of highlighting

2. Which of the following is the correct syntax to use ts_headline to highlight the word 'apple' in the text column 'description' with the 'english' configuration?

easy

A. SELECT ts_headline(description, 'apple', 'english') FROM products;

B. SELECT ts_headline('apple', description) FROM products;

C. SELECT ts_headline(description, to_tsquery('apple')) FROM products;

D. SELECT ts_headline(description, 'apple') FROM products;

Solution

1. Step 1: Recall ts_headline syntax

   The function syntax is ts_headline(text, query [, config]), where query is a tsquery type or text query string, and config is optional language.

2. Step 2: Analyze options

   SELECT ts_headline(description, 'apple', 'english') FROM products; uses text, query string, and language config correctly. SELECT ts_headline(description, 'apple') FROM products; misses language but is valid; however, for 'english' configuration, it requires the config. SELECT ts_headline('apple', description) FROM products; has arguments reversed. SELECT ts_headline(description, to_tsquery('apple')) FROM products; uses to_tsquery but misses language config.

3. Final Answer:

   SELECT ts_headline(description, 'apple', 'english') FROM products; -> Option A

4. Quick Check:

   Correct syntax with language = A [OK]

Hint: Use ts_headline(text, query, 'language') for best results [OK]

Common Mistakes:

• Swapping text and query arguments
• Omitting language config when needed
• Passing plain text instead of tsquery or query string

3. Given the table articles with a column content containing 'PostgreSQL is a powerful database system.', what is the output of this query?

SELECT ts_headline(content, 'database') FROM articles;

medium

A. PostgreSQL is a powerful database system.

B. PostgreSQL is a powerful database system.

C. PostgreSQL is a powerful database system.

D. Error: ts_headline requires a tsquery type

Solution

1. Step 1: Understand ts_headline output

   By default, ts_headline highlights matched words with tags.

2. Step 2: Analyze the query and text

   The word 'database' matches the query 'database', so it will be wrapped in tags.

3. Final Answer:

   PostgreSQL is a powerful database system. -> Option C

4. Quick Check:

   Highlight uses tags = A [OK]

Hint: Matched words get <b> tags by default in ts_headline [OK]

Common Mistakes:

• Expecting no tags or different tags like <em>
• Thinking ts_headline returns plain text without highlights
• Assuming ts_headline needs explicit tsquery always

4. You run this query:

SELECT ts_headline(content, to_tsquery('english', 'search')) FROM documents;

But get an error: function ts_headline(text, tsquery) does not exist. What is the likely cause?

medium

A. The ts_headline function requires a text query string, not tsquery

B. The ts_headline function does not accept to_tsquery results directly

C. The ts_headline function requires three arguments including language

D. The to_tsquery function is used incorrectly without casting

Solution

1. Step 1: Check function signature

   ts_headline accepts text and tsquery but sometimes requires explicit casting or correct argument types.

2. Step 2: Identify error cause

   The error suggests no matching function for ts_headline(text, tsquery). This often happens if to_tsquery is used without proper casting or language config mismatch.

3. Final Answer:

   The to_tsquery function is used incorrectly without casting -> Option D

4. Quick Check:

   Incorrect to_tsquery usage = B [OK]

Hint: Cast to_tsquery output properly for ts_headline [OK]

Common Mistakes:

• Assuming ts_headline accepts any tsquery without casting
• Forgetting to match language configs
• Confusing argument order or types

5. You want to highlight multiple search terms 'fast' or 'reliable' in a product description using ts_headline. Which query correctly highlights both terms in the description column?

hard

A. SELECT ts_headline(description, to_tsquery('english', 'fast | reliable')) FROM products;

B. SELECT ts_headline(description, to_tsquery('fast & reliable')) FROM products;

C. SELECT ts_headline(description, 'fast & reliable', 'english') FROM products;

D. SELECT ts_headline(description, 'fast reliable') FROM products;

Solution

1. Step 1: Understand tsquery for multiple terms

   To highlight multiple terms, use to_tsquery with operators like | (OR) or & (AND).

2. Step 2: Analyze options for correct syntax

   SELECT ts_headline(description, to_tsquery('english', 'fast | reliable')) FROM products; uses to_tsquery('english', 'fast | reliable') which searches for either term correctly. SELECT ts_headline(description, 'fast & reliable', 'english') FROM products; uses plain string with & which is invalid. SELECT ts_headline(description, to_tsquery('fast & reliable')) FROM products; misses language config. SELECT ts_headline(description, 'fast reliable') FROM products; uses plain string without operators.

3. Final Answer:

   SELECT ts_headline(description, to_tsquery('english', 'fast | reliable')) FROM products; -> Option A

4. Quick Check:

   Use to_tsquery with OR operator = C [OK]

Hint: Use to_tsquery with | to highlight multiple terms [OK]

Common Mistakes:

• Passing plain strings without to_tsquery for multiple terms
• Using & in plain strings instead of tsquery syntax
• Omitting language in to_tsquery