6

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.

Community
  • 1
  • 1
Gruntled
  • 143
  • 9
  • Did you find an answer to this? I am running into the same issue. – John Goodman May 30 '12 at 20:25
  • 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. – Gruntled May 31 '12 at 12:16
  • 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. – John Goodman May 31 '12 at 19:48

1 Answers1

11

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.

stephenalexbrowne
  • 1,191
  • 2
  • 10
  • 22
  • 2
    I'm the author and maintainer of pg_search, and what @stephenalexbrowne says is exactly correct. More info is available at https://github.com/Casecommons/pg_search/wiki/Installing-Postgres-Contrib-Modules – Grant Hutchins Sep 09 '12 at 22:36
  • 1
    @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. – Simpleton Dec 11 '12 at 09:53
  • @Simpleton Usually you would be see something like `text % text`, not `text % unknown`. Maybe you could open an issue at https://github.com/Casecommons/pg_search/issues and paste in your query? – Grant Hutchins Dec 16 '12 at 19:41
  • Cool, will open an issue later today if I get time. – Simpleton Dec 18 '12 at 06:50