PostgreSQL ts_headline not working?

Written on Thu 04 December 2008

I was trying some examples on PosgreSQL documentation about highlighting search terms in a text. I would like to do full-text search on documents and have them highlighted by PostgreSQL automatically so that I could show them right away on a web page. The trouble was, the examples were not working out correctly for me. Here's the correct running:

SELECT ts_headline('english', 'The most common type of search is to find all documents containing given query terms and return them in order of their similarity to the query.', to_tsquery('query & similarity')); ts_headline ------------------------------------------------------------ given query terms and return them in order of their similarity to the query.

But here's what I was getting:

SELECT ts_headline('english', 'The most common type of search is to find all documents containing given query terms and return them in order of their similarity to the query.', to_tsquery('query & similarity')); ts_headline -------------------------------------------------------------------------------------- The most common type of search is to find all documents containing given query terms (1 row)

After googling it for some time I found the reason. It is because of the default search configuration I was using. Since it is not "english", the to_tsquery function does not work as expected.

=> show default_text_search_config; default_text_search_config ---------------------------- pg_catalog.turkish (1 row) => select to_tsquery('query & similarity'); to_tsquery ------------------------ 'query' & 'similarity' (1 row) => select to_tsquery('english', 'query & similarity'); to_tsquery --------------------- 'queri' & 'similar' (1 row)

Aha. Now we can retry:

SELECT ts_headline('english', 'The most common type of search is to find all documents containing given query terms and return them in order of their similarity to the query.', to_tsquery('english', 'query & similarity')); ts_headline ---------------------------------------------------------------------------------------------------- given query terms and return them in order of their similarity to the query. (1 row)

Now that's better.