0

I've a problem in my DB (PGSQL 9.6) that I've not been able to resolve and I don't really even understand what options I might have (if any). Simplified I have a DB of multi-language news articles where I've created full text search index (tsvector) on headlines and text. I need to search through the DB to find articles with a certain game name.

Example of an index that would match what I do:

SELECT to_tsvector('simple', 'Yakumo Shionome featured in new Code Vein-trailer');

This results in the following full text search index:

'code':6 'features':3 'in':4 'new':5 'shionome':2 'trailer':9 'vein':8 'vein-trailer':7 'yakumo':1

The challenge come when I want to search for "Code Vein" as game title. In this case I would normally use "phraseto_tsquery". A raw test query could be similar to this:

SELECT to_tsvector('simple','Yakumo Shionome featured in new Code Vein-trailer') 
  @@ phraseto_tsquery('simple','Code Vein');

This would result in FALSE in this case. I would have to search for "Code Vein-Trailer" to get a TRUE match. So the "hyphen" followed by "some word" in essence is causing me trouble and I don't really get what options I might have as far as creating a way around this (there can be many different ending words, many I also might not know ahead of time).

Normally the fact that my Boolean searches are word boundary based is good as it avoid potential false positives, but this seems to be a challenging drawback. I use "simple" due to multiple languages and that game names and elements I sometime search for included stop words - which I felt ended up being problematic or more prune to issues if not taking this approach. I also do require exact matches and not ranked results.

Any thoughts and help welcome.

  • problem appears to be that the original text is malformed: someone used a hyphen '-' instead of a dash. ' - ' if you don't mean 'Vein-trailer' don't write 'Vein-trailer' – Jasen Jul 31 '18 at 20:34
  • perhaps this question is one for english.stackexchange.com – Jasen Jul 31 '18 at 20:40
  • I think that's beyond the point here, adding a hypen or dash for some might means the same and for what I know in Hungarian or Italian the rules of use might differ. In the end and as long as used without space it causes the same issue on my end. – organizer Aug 1 '18 at 10:04
1

The problem is that the token 'vein-trailer' gets emitted and is located before the bare token 'vein' is, so is between 'code' and 'vein'.

One way to fix this is to make a new text search configuration which doesn't emit hyphenated words:

create text search configuration simple2 (copy=simple);
alter text search configuration simple2 drop mapping for asciihword;

Then the following returns true:

select 
  to_tsvector('simple2','Yakumo Shionome featured in new Code Vein-trailer') 
  @@
  phraseto_tsquery('simple2','Code Vein');

Whether this will fix all your problems, or create new problems, is hard to tell based on your description and also hard to intuit, but it does fix the literal problem you presented. You probably need to do something with non-ascii hwords as well.

  • Thank you for this, definitively giving me something to think about and I'll do some real case testing with this. At glace it does fix the problem, but I'm not sure I fully grasp what potential issues this might cause ... – organizer Aug 1 '18 at 10:11
  • If I need to make your solution unaccented, would I simple do a ALTER TEXT SEARCH CONFIGURATION simple2 ALTER MAPPING FOR hword, hword_part, word WITH unaccent, simple? @jjanes – organizer Aug 1 '18 at 13:44

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.