patternsqlMinor
Postgres ts_headline inconsistent behaviour
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):
But a different word works fine...
Does anybody have an explanation for this behaviour?
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:
which of course can not be found in the text converted by simple dictionary.
So the correct query would have been:
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.