Highlighting with ts_headline in PostgreSQL - Step-by-Step Execution

Concept Flow - Highlighting with ts_headline

Input: Text + Search Query

↓

Parse Query into Search Terms

↓

Search Text for Terms

↓

Wrap Matching Terms with Highlight Tags

↓

Output: Highlighted Text Snippet

The function takes text and a search query, finds matching words, and wraps them with highlight tags to show where matches occur.

Execution Sample

PostgreSQL

SELECT ts_headline(
  'The quick brown fox jumps over the lazy dog',
  to_tsquery('fox | dog')
);

This query highlights the words 'fox' and 'dog' in the given sentence.

Execution Table

Step	Action	Input	Output/State
1	Receive text and query	'The quick brown fox jumps over the lazy dog', 'fox | dog'	Ready to process
2	Parse query	'fox | dog'	Search terms: ['fox', 'dog']
3	Search text for terms	Text, terms ['fox', 'dog']	Found 'fox' at position 17, 'dog' at position 40
4	Wrap matches with highlight tags	Positions 17 and 40	Text with <b>fox</b> and <b>dog</b>
5	Return highlighted snippet	Modified text	'The quick brown <b>fox</b> jumps over the lazy <b>dog</b>'

💡 All matching terms highlighted and output returned

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
text	'The quick brown fox jumps over the lazy dog'	'The quick brown fox jumps over the lazy dog'	'The quick brown fox jumps over the lazy dog'	'The quick brown <b>fox</b> jumps over the lazy <b>dog</b>'	'The quick brown <b>fox</b> jumps over the lazy <b>dog</b>'
query	'fox | dog'	'fox | dog'	'fox | dog'	'fox | dog'	'fox | dog'
search_terms	N/A	['fox', 'dog']	['fox', 'dog']	['fox', 'dog']	['fox', 'dog']
positions	N/A	N/A	[16, 40]	[16, 40]	[16, 40]

Key Moments - 3 Insights

Why does ts_headline highlight only 'fox' and 'dog' but not other words?

Because ts_headline uses the parsed search query terms (see execution_table step 2) to find matches. Only 'fox' and 'dog' are in the query, so only those get highlighted.

What happens if the search query has multiple words separated by | (OR)?

The query is parsed into separate terms (step 2), and ts_headline highlights any word matching any of those terms (step 3 and 4).

Why are highlight tags like <b> used in the output?

ts_headline wraps matching words with tags (step 4) to visually emphasize them when displayed, making it easy to spot matches.

Visual Quiz - 3 Questions

Look at the execution_table, what are the search terms after parsing the query?

A ['jumps', 'lazy']

B ['quick', 'brown']

C ['fox', 'dog']

D ['the', 'over']

💡 Hint

Check the 'Parse query' step in the execution_table (Step 2)

At which step does ts_headline wrap the matching words with highlight tags?

A Step 3

B Step 4

C Step 2

D Step 5

💡 Hint

Look for the step mentioning wrapping matches with tags in execution_table

If the query was changed to 'quick | lazy', which words would be highlighted?

A 'quick' and 'lazy'

B 'fox' and 'dog'

C 'brown' and 'jumps'

D 'the' and 'over'

💡 Hint

Refer to how search_terms affect highlighted words in variable_tracker and key_moments

Concept Snapshot

ts_headline(text, query) highlights words in text matching the query.
The query is parsed into search terms.
Matching words are wrapped with highlight tags (default <b>).
Useful for showing search results with emphasized matches.
Works with tsquery input for flexible search patterns.

Full Transcript

The ts_headline function in PostgreSQL takes a text and a search query as input. It first parses the query into individual search terms. Then it searches the text for these terms. When it finds matches, it wraps those words with highlight tags, usually <b> tags, to emphasize them. Finally, it returns the text snippet with highlighted words. This helps users quickly see where their search terms appear in the text. For example, given the sentence 'The quick brown fox jumps over the lazy dog' and the query 'fox | dog', ts_headline highlights 'fox' and 'dog' in the output. The process involves parsing, searching, wrapping, and returning the highlighted snippet.