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.

Christos

Did you find an answer to this? I am running into the same issue. – Scallop

Hey John. I'm trying to recall, but I think I fixed this by removing the trigram functionality (whatever that is). So deleting this line: :trigram => {} fixed it for me. – Christos

Ya, digging deeper I found that you have to install the trigram package for it to work. The pg_search page has a little bit of documentation on it. – Scallop

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.

Insolvency

I'm the author and maintainer of pg_search, and what @Insolvency says is exactly correct. More info is available at github.com/Casecommons/pg_search/wiki/… – Inelegancy

@nertzy I have followed this to the T and it breaks locally and on Heroku with the PG::Error: ERROR: operator does not exist: text % unknown error. I'm on the shared DB that offers the trigram package and I have PG installed via homebrew with the trigram package there. – Barretter

@Barretter Usually you would be see something like text % text, not text % unknown. Maybe you could open an issue at github.com/Casecommons/pg_search/issues and paste in your query? – Inelegancy

Cool, will open an issue later today if I get time. – Barretter