Re: Question about fulltext search and to_tsvector function

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(at)comcast(dot)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
>
>