Highlighting with ts_headline in PostgreSQL

Introduction

Highlighting helps you see where your search words appear in text by marking them clearly.

When showing search results to users and you want to highlight matching words.

When you want to quickly find important parts in a large text based on a search query.

When building a search feature that shows snippets with highlighted keywords.

When you want to improve readability of search results by emphasizing matched terms.

Syntax

PostgreSQL

ts_headline([config,] document, query [, options])

document is the text you want to search and highlight.

query is the search condition, usually a tsquery type or text converted to tsquery.

Examples

Highlights the word 'fox' in the given sentence.

PostgreSQL

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

Uses English text search configuration for better word recognition.

PostgreSQL

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

Highlights both 'fox' and 'dog' using a tsquery with AND operator.

PostgreSQL

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

Sample Program

This query highlights the words 'open' and 'database' in the sentence using English configuration.

PostgreSQL

SELECT ts_headline('english', 'PostgreSQL is a powerful, open source object-relational database system.', to_tsquery('open & database'));

Important Notes

By default, ts_headline wraps highlighted words in and tags.

You can customize the highlight tags using the 'StartSel' and 'StopSel' options.

Make sure to use the correct text search configuration for your language to get better results.

Summary

ts_headline highlights search terms in text to make them easy to spot.

It takes the text, a search query, and optional language settings.

Use it to improve user experience in search results by showing matched words clearly.