Re: Question about fulltext search and to_tsvector function - Mailing list pgsql-general

That was it!

So this is the standard way of doing this?  It seems a bit messy to me, but I've gotten kinda used to Postgresql being almost annoyingly logical <g>

So here's my new query:

SELECT R.RecipeId, R.Title, R.Description FROM Recipes R
WHERE (to_tsvector('english', title || ' ' || coalesce(description, '') || ' ' || coalesce(credit, '') || ' ' || coalesce(steps, '')) @@ plainto_tsquery('cake'));

and my new index:

CREATE INDEX idx_recipes_fullsearch
  ON recipes
  USING gin
  (to_tsvector('english'::regconfig, ((title::text || ' ' || COALESCE(description, ''::character varying)::text) || ' ' || COALESCE(credit, ''::character varying)::text) || ' ' || COALESCE(steps, ''::text)));


This is exactly what you would do as well?  I just want to make sure this isn't considered a hack..

Tom Lane wrote:

Mike Christensen <imaudi@comcast.net> writes: 

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'));   

Maybe it would be better to do description || ' ' || title ?
I suspect that 'Cake' is getting run together with some other
word in some of your rows ...
		regards, tom lane