to_tsquery for postgresql formatting

Question

I've searched the postgreSQL documentation (on v9.6) for use of to_tsquery() as well as a number of other sources (via the Google search engine). I have found hints of others experiencing similar problems, but I could use help perhaps just understanding the fundamental nature of the to_tsquery postgreSQL syntax.

if I query

SELECT * from table
WHERE table.email @@ to_tsquery('[email protected]');

then I get all rows where the email column contains '[email protected]'.

But if I query

SELECT * from table
WHERE table.email @@ to_tsquery('user:* & domain:* & ext:*');

then I get no rows.

Can anyone offer any guidance on how I may be misusing to_tsquery in the second case? My intention is to simply drop @ and . from the email address and MATCH on the cases where usr, domain, and ext match the email.

Thank you for any guidance!

--J

Answer 1

As the PostgreSQL committer replied in the discussion you linked to:

tsvector is meant for searching for natural-language words. It is not a good idea to imagine that it works exactly like a substring match, especially on strings that aren't simple words.

The text search parser recognizes [email protected] in a larger text as a token of type email. It doesn't provide any function to extract the top-level domain or sub-domains or local address part.

Besides, it's not clear what's your use case. Matching a locale part against prefix might make sense, but matching a top level domain against a prefix? .co as a TLD hasn't any more to do with .com than with .org or .info, so ext:* is not going to achieve much as match pattern.

Also subdomains are hierarchical in nature. ibm can be found in something.ibm.com, which doesn't match ibm:* , so that kind of match doesn't appear very useful either.