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