HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Postgres ts_headline inconsistent behaviour

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
ts_headlinebehaviourpostgresinconsistent

Problem

I have just stumbled over a weird behaviour in postgres ts_headline function (used for highlighting fulltext search results).
First I thought the simple dictionary does not play with highlighting as the below example shows (there should be `` tags):

# SELECT ts_headline('simple', 'This is artificial text', to_tsquery('artificial'));
       ts_headline       
-------------------------
 This is artificial text
(1 row)


But a different word works fine...

# SELECT ts_headline('simple', 'some Word in', to_tsquery('Word'));
 ts_headline     
---------------------
 some Word in
(1 row)


Does anybody have an explanation for this behaviour?

Solution

I noticed my mistake.
The to_tsquery function inside also needs to know which dictionary to use (aka use the simple one). By default it uses english stemming which produces this result:

SELECT to_tsquery('artificial');
 to_tsquery 
------------
 'artifici'


which of course can not be found in the text converted by simple dictionary.
So the correct query would have been:

# SELECT ts_headline('simple', 'This is artificial text', to_tsquery('simple', 'artificial'));
          ts_headline           
--------------------------------
 This is artificial text
(1 row)

Code Snippets

SELECT to_tsquery('artificial');
 to_tsquery 
------------
 'artifici'
# SELECT ts_headline('simple', 'This is artificial text', to_tsquery('simple', 'artificial'));
          ts_headline           
--------------------------------
 This is <b>artificial</b> text
(1 row)

Context

StackExchange Database Administrators Q#176059, answer score: 8

Revisions (0)

No revisions yet.