From: | Alastair McKinley <a(dot)mckinley(at)analyticsengines(dot)com> |
---|---|
To: | "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | websearch_to_tsquery() and handling of ampersand characters inside double quotes |
Date: | 2019-10-10 14:23:56 |
Message-ID: | DB6PR0202MB290450F7668104992B9D6F3CE3940@DB6PR0202MB2904.eurprd02.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Hi all,
I have recently discovered an unexpected difference in behaviour using websearch_to_tsquery() with quoted strings containing ampersands.
These two queries are equivalent without surrounding double quotes
select websearch_to_tsquery('something and another') = websearch_to_tsquery('something & another');
?column?
----------
t
(1 row)
select websearch_to_tsquery('something and another');
websearch_to_tsquery
----------------------
'someth' & 'anoth'
(1 row)
With surrounding double quotes they produce subtly different queries, with different positional information.
select websearch_to_tsquery('"something and another"') = websearch_to_tsquery('"something & another"');
?column?
----------
f
(1 row)
select websearch_to_tsquery('"something and another"');
websearch_to_tsquery
----------------------
'someth' <2> 'anoth'
(1 row)
select websearch_to_tsquery('"something & another"');
websearch_to_tsquery
----------------------
'someth' <-> 'anoth'
(1 row)
I imagine the difference is due to the ts_vector type recording different information for the underlying strings.
select to_tsvector('something & another');
to_tsvector
----------------------
'anoth':2 'someth':1
(1 row)
chimera=# select to_tsvector('something and another');
to_tsvector
----------------------
'anoth':3 'someth':1
(1 row)
This leads to quite different search results and my current workaround is to suggest to users to do both searches with an OR. Is this the right solution?
Best regards,
Alastair
From | Date | Subject | |
---|---|---|---|
Next Message | Stephen Frost | 2019-10-10 14:35:04 | Re: Is my lecturer wrong about PostgreSQL? I think he is! |
Previous Message | Alastair McKinley | 2019-10-10 14:10:04 | Re: websearch_to_tsquery() and apostrophe inside double quotes |