I am using full-text search against a single table column. The data in the column could be of any language with no way to determine the language ahead of time. Assuming I only care about some subset of possible languages (for example English and Spanish), I can easily generate a list of lexemes for the vector:

to_tsvector('english', my_column) || to_tsvector('spanish', my_column)

and for the query:

to_tsquery('english', 'something') || to_tsquery('spanish', 'something')

This works as expected.

However, I would also like to return the column value with the matches highlighted, but ts_headline accepts a single configuration, the document and the query as parameters. I can write something like:

  to_tsquery('english', 'amor') || to_tsquery('spanish', 'amor')

but this will only apply the highlighting based on the configuration passed to ts_headline -- so in the example above, the word "amoroso" will be included in the result, but will only be highlighted if I set the configuration to "spanish".

I could run ts_headline for each configuration and return the results as an array, but then I would still have to combine them after running the query, which is less than ideal.

Is there a way to apply the highlighting based on multiple languages?

EDIT: Alternatively, given a document, its lexemes and the query lexemes, is there a way to return the list of matched words in the document along with their locations in the document?

1 Answer 1


You could do something like

case ts_rank(t."document_en", to_tsquery('english', p_query_or)) > ts_rank(t."document_fr", to_tsquery('french', p_query_or))
           when true then ts_headline('english', t."title", to_tsquery('english', p_query_or))
           else ts_headline('french', t."title", to_tsquery('french', p_query_or))
        end as title
  • Curious pattern: CASE <condition> WHEN true THEN ... ELSE ... END. Why not just CASE WHEN <condition> THEN ... ELSE ... END?
    – Andriy M
    Commented Jun 5, 2021 at 20:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.