From: | Mike Christensen <imaudi(at)comcast(dot)net> |
---|---|
To: | |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Question about fulltext search and to_tsvector function |
Date: | 2008-10-17 03:42:49 |
Message-ID: | 48F809B9.3080609@comcast.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Mike Christensen | 2008-10-17 03:53:27 | Re: Question about fulltext search and to_tsvector function |
Previous Message | Scott Marlowe | 2008-10-17 03:40:08 | Re: Question about NOT NULL and default values. |