Re: Question about fulltext search and to_tsvector function

Hi guys -

I half figured this out..  The problem is the vector is concatenating
the fields together..  If I have a title of "Iced Lemon Cake" and the
description is "Tangy and sweet!", then the tsvector looks like this:

"'ice':1 'lemon':2 'sweet':5 'caketangi':3"

"Cake" and "Tangy" get parsed as "caketangi" heheh..

So my questions are:

1) What's the proper syntax for this query, and
2) What's the proper syntax to index the vector (I can probably figured
this out once I have the first answer)..

Thanks!!

Mike



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
>