Mike,
1. always separate attributes by blank unless you sure words can span different
columns, e.g., title||' '||description
2. use coalesce to avoid unexpected NULL string
Oleg
On Thu, 16 Oct 2008, Mike Christensen wrote:
Okay this one's driving me crazy.
Should there be any difference between the following queries:
SELECT R.Title FROM Recipes R
WHERE (to_tsvector('english', title || description) @@
plainto_tsquery('Cake'));
and
SELECT R.Title FROM Recipes R
WHERE (to_tsvector('english', description || title) @@
plainto_tsquery('Cake'));
This query should search for the word 'Cake' in either the description OR the
title field, correct? The order should not matter. However, in the first
query I get 6 results and in the second I get 9.
Furthermore, if I do:
SELECT R.Title FROM Recipes R
WHERE (to_tsvector('english', title) @@ plainto_tsquery('Cake'));
I get 10 results and if I do:
SELECT R.Title FROM Recipes R
WHERE (to_tsvector('english', title || coalesce(description, '')) @@
plainto_tsquery('Cake'));
I get 7 results. Shouldn't the first query result in a subset of the second
query? The first query returns several rows the second one doesn't return,
and vice-versa! This function is completely confusing me!!
I would greatly appreciate anyone who could explain exactly how this works.
It's most likely something stupid I'm doing that is the result of me not
getting enough sleep..
Mike
Regards,
Oleg
_____________________________________________________________
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@xxxxxxxxxx, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
