3

Trying to figure out, why the below query isn't returning any results:

select id from story where tsv_story_text @@ to_tsquery('CumEx-Files<->2.0');

The term exists in the headline of a story in the database (PostgreSQL 11.12) like:

CumEx-Files 2.0 investigation reveals €150bn tax fraud

The only way I could get the query to work was to replace the hyphen with a dash in both the vector and the query which worked, so I tried debugging the phrase, but it seems like the hyphen is but a space symbol - I thought it has to do something with the numerals in the phrase, or is it?

SELECT * FROM ts_debug('english', 'CumEx-Files<->2.0');
      alias      |           description           |    token    |  dictionaries  |  dictionary  |   lexemes
-----------------+---------------------------------+-------------+----------------+--------------+-------------
 asciihword      | Hyphenated word, all ASCII      | CumEx-Files | {english_stem} | english_stem | {cumex-fil}
 hword_asciipart | Hyphenated word part, all ASCII | CumEx       | {english_stem} | english_stem | {cumex}
 blank           | Space symbols                   | -           | {}             |              |
 hword_asciipart | Hyphenated word part, all ASCII | Files       | {english_stem} | english_stem | {file}
 blank           | Space symbols                   | <           | {}             |              |
 blank           | Space symbols                   | ->          | {}             |              |
 float           | Decimal notation                | 2.0         | {simple}       | simple       | {2.0}
8
  • It works for me: SELECT to_tsvector('english', 'CumEx-Files 2.0 investigation reveals €150bn tax fraud') @@ to_tsquery('english', 'CumEx-Files<->2.0'); gives me "true". Nov 17 at 10:22
  • this is weird, pasted the above query into my terminal and I get false for ?column? Nov 17 at 11:29
  • @LaurenzAlbe, what version of PostgreSQL are you using? Nov 17 at 11:36
  • 1
    Noticed that phraseto_tsquery() - works instead of to_tsquery(), which ties all tokens via <->. Mimicked this behaviour and I manage to get a match for my hyphenated queries. I'm wondering, how was this working for you? Nov 17 at 15:59
  • 2
    @LaurenzAlbe: I am using 14.1, but that behavior hasn't changed, as far as I know. It has changed. Nov 17 at 23:03
2

There was change of behavior in Postgres 14. (A fix, really, but not back-patched, because change of behavior ...)

The release notes for Postgres 14:

  • Fix to_tsquery() and websearch_to_tsquery() to properly parse query text containing discarded tokens (Alexander Korotkov)

    Certain discarded tokens, like underscore, caused the output of these functions to produce incorrect tsquery output, e.g., both websearch_to_tsquery('"pg_class pg"') and to_tsquery('pg_class <-> pg') used to output ( 'pg' & 'class' ) <-> 'pg', but now both output 'pg' <-> 'class' <-> 'pg'.

That's exactly what happens in your case. Compare the results in the following two fiddles:

db<>fiddle here - Postgres 11

db<>fiddle here - Postgres 14

So your query:

SELECT to_tsvector('english', 'CumEx-Files 2.0 investigation reveals €150bn tax fraud') @@ to_tsquery('english', 'CumEx-Files<->2.0');

... now works as expected in Postgres 14 or later (returns true). Consider upgrading.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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