Question about fulltext search and to_tsvector function

From: Mike Christensen <imaudi(at)comcast(dot)net>
To: pgsql-general(at)postgresql(dot)org
Subject: Question about fulltext search and to_tsvector function
Date: 2008-10-17 03:31:10
Message-ID: 48F806FE.4080004@comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2008-10-17 03:36:15 Re: Question about NOT NULL and default values.
Previous Message Tim Uckun 2008-10-17 03:26:37 Re: Question about NOT NULL and default values.