From: | Raymond Lu <raylu(at)benchling(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Text search lexer's handling of hyphens and negatives |
Date: | 2019-10-14 21:28:27 |
Message-ID: | CAAmRUjt8XNGWKUiPA3uQ6+7UaPTNOsrfQVF-gPcS9zZnM5PW3Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
We've been happily using pgsql to store user-generated documents for a
while now. We also wanted to be able to search the documents so we
tossed the document contents into a tsvector and did a pretty
straightforward contents @@ phraseto_tsquery('simple', 'the query').
Our users have a lot of things named like ABC-DEF-GHI so that sort of
hyphenated name appears in their documents fairly often.
to_tsvector('simple', 'ABC-DEF-GHI') @@ phraseto_tsquery('simple',
'ABC-DEF-GHI') works without issue.
Sometimes, these hyphenated names have numbers in them like
UVW-789-XYZ. Still no problem with to_tsvector/phraseto_tsquery.
Sometimes, users can only remember the last bit of the name. So they'd
like to find the document with ABC-DEF-GHI in it by searching for
'DEF-GHI'. Since to_tsvector('simple', 'ABC-DEF-GHI') is
'abc-def-ghi':1 'abc':2 'def':3 'ghi':4
we search for to_tsquery('simple', 'def <-> ghi') instead of using
phraseto_tsquery. This works, but you can probably see where this is
going.
to_tsvector('simple', 'UVW-789-XYZ') is
'uvw':1 '-789':2 'xyz':3
because -789 is a negative integer. If we turn the query '789-XYZ'
into the tsquery as before, we get to_tsquery('simple', '789 <-> xyz')
which doesn't match it.
Are we missing something here? Is there either a way to
1. generate tsvectors without this special (negative) integer behavior or
2. generate tsqueries in a more intelligent way?
From | Date | Subject | |
---|---|---|---|
Next Message | David Rowley | 2019-10-14 21:35:51 | Re: How to make runtime partition pruning work? |
Previous Message | Lu, Dan | 2019-10-14 18:22:20 | RE: Postgres 10.7 Systemd Startup Issue |