1

I was quite surprised to find that TO_TSVECTOR('english', '平仮名ひらがな'); results in a vector of '平仮名ひらがな':1. For a system that omitted all non-alphanumeric characters and emojis I threw at it, I expected it to also omit all foreign characters; i.e. the desired behavior is to only index alphanumeric characters.

How does it decide which characters to omit (is it a whitelist/blacklist, a range of character codes, etc)? I don't really mind it automatically converting accented characters to their normal alphanumeric counterpart, but I would like to efficiently omit all characters that don't have an alphanumeric representation.


edit - based from jjane's answer, this is the final solution I'm going with:

CREATE TEXT SEARCH CONFIGURATION basic (parser = pg_catalog.default);
ALTER TEXT SEARCH CONFIGURATION basic ADD MAPPING FOR asciiword, asciihword, hword_asciipart WITH english_stem;
--optionally ALTER TEXT SEARCH CONFIGURATION basic ADD MAPPING FOR email, host WITH english_stem;
  • The results of the default parser also depend on the lc_ctype setting of the database. – Daniel Vérité Apr 19 at 17:00
2

They are alphanumeric in some alphabet, just not the one you want to use.

You can alter your text search configuration to drop the types of tokens you do not want. You should probably copy and alter, not alter in place.

CREATE TEXT SEARCH CONFIGURATION ascii_only ( COPY = pg_catalog.english );
alter text search configuration ascii_only drop mapping for word;
select * from to_tsvector('ascii_only', '平仮名ひらがな dogs');

Yields:

"'dog':1"

This is just a minimal example, you will probably need to drop the mappings for other token types as well, chosen from this list. You might not be able to get exactly what you want, for example there is no asciinumword, so there is no way keep 'dog1' but reject '平仮名ひらがな1'. So in that case you would have to implement your own non-default parser, or use functions (hopefully making them inefficient is optional--we might be able to help with that).

You can use ts_debug to see what is going on. A word that is entirely ASCII letters [a-zA-Z] gets tokenized to 'asciiword', and not tokenized to 'word'. So a better name for 'word' might be 'word excluding asciiword'. So by dropping the mapping for 'word', you are excluding words other than asciiwords. This means dropping not only words that are entirely foreign, but also words with accents. It sounded like you were OK with either dropping the accent, or dropping the entire word, but maybe not.

But something with a mixture of letters and numbers is not tokenized to either 'word' or 'asciwword', but rather to 'numword', so would not be excluded unless you also dropped 'numword' in addition to 'word'.

If these don't do what you want and you need to use a function, I don't know what you want the function to do precisely, so it is hard to give an example. If you go that route and implement the function that does what you want and find it not efficient enough, you can start a new post on that topic.

  • I tried dog1 and it gets tokened as dog1 for me with version 11.2 on Ubuntu. URL's and IP's still get tokened properly. I think this already does exactly what I wanted to, but I wouldn't mind if you added the solution that uses functions for future readers – dvtan Apr 18 at 13:53
  • Though I still don't quite understand how doing drop mapping for word is able to solve this, could you please explain what that does? – dvtan Apr 18 at 13:59
  • The problem would be that '平仮名ひらがな1' also gets included along with 'dog1'. It is easy to include both or neither, but not to include one but not the other. – jjanes Apr 18 at 14:05
  • Oh I see what you mean now... that "1" would cause it to get picked up by numword, and we can't just drop numword since there's no numasciiword. So what would the other solution using functions look like? – dvtan Apr 18 at 14:15
  • I played around with ts_debug and looks like I only need asciiword, asciihword, and hword_asciipart since this website doesn't really use numwords for anything. So this solution is perfect for now. I might need it in a future project, but that's for another time. Thanks for your help! – dvtan Apr 18 at 21:49

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.