Error with pg_search multisearch after initializing tsearch, trigram

Question

I had pg_search working on my Rails 3.2.3 app using multisearch. Then I implemented the initializer provided by nertzy (author of pg_search) in this post.. Now when I run a search I get the following error:

PG::Error: ERROR:  operator does not exist: text % unknown
LINE 1: ... ((coalesce("pg_search_documents"."content", '')) % 'searchterm...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.

My view is rendered with this code:

<%= @pg_search_documents.each do |pg_search_document| %>
  <%= pg_search_document.searchable.title %>
<% end %>

The rest of my setup can be found here. Any help is much appreciated.

Answer 1

I ran into this problem before too. Just to clarify for anyone else who might be running into trouble... here's how to install the extension:

1. Create a new migration by running

   bundle exec rails g migration add_trigram_extension
   

2. In your migration, paste the following code:

   def up
       execute "create extension pg_trgm"
   end
   
   def down
       execute "drop extension pg_trgm"
   end
   

3. Run the migration with bundle exec rake db:migrate

This worked for me locally. Some of the extensions or configurations you can use with pg_search require newer versions of Postgres. In order to use certain extensions on heroku, you may need to use a dev database.

UPDATE: It is my understanding that heroku has issued rolling upgrades and now everyone is running a newer version of pg by default. The above should work on heroku without the need to upgrade your database.