pg_search and multi column indexing (Rails 4)

Question

I have a full-text search query in PostgreSQL that looks like this:

to_tsvector('english', coalesce("products"."name"::text, '')) || to_tsvector('english', coalesce("products"."uid"::text, '')) || to_tsvector('english', coalesce("products"."serial"::text, ''));

But creating a migration like this does not work for some reason:

create_trigger(compatibility: 1).on(:products).before(:insert, :update) do
  "new.tsv_body := to_tsvector('english', coalesce("products"."name"::text, '')) || to_tsvector('english', coalesce("products"."uid"::text, '')) || to_tsvector('english', coalesce("products"."serial"::text, ''));"
end

Any advice?

whenever you write "does not work", that's a sign you need to delete those words and replace them with an actual description of why it does not work. What does it do / not do? What effect did you expect? What actually happened? Were there any error messages or log messages? — Craig Ringer, Jan 21 '14 at 02:24
Point taken. It was a terribly posted question. The answer below worked, when I commented out the double-quotes the migration ran. Then I tried to run: Product.find_each(&:touch) in the console and got this error: PG::UndefinedTable: ERROR: missing FROM-clause entry for table "products" — John Trichereau, Jan 21 '14 at 12:31

score 1 · Accepted Answer · answered Jan 21 '14 at 02:26

The hint is in how Stack Overflow has syntax-highlighted your program text:

  "new.tsv_body := to_tsvector('english', coalesce("products"."name"::text, '')) .... )
                                                   ^^^^^^^^^^ ^^^^^^

You've included double-quotes around the table and column names. The whole string is in double quotes. So these inset double quotes end the string.

You'll need to escape them, or omit them. I don't do Ruby / Rails, but if it's anything like most languages, backslash escapes are appropriate:

"new.tsv_body := to_tsvector('english', coalesce(\"products\".\"name\"::text, '')) .... )

pg_search and multi column indexing (Rails 4)

1 Answers1