CODEWITHSUNDEEP
ruby-on-railspostgresqlruby-on-rails-4
coding addicted
coding addicted

Reputation: 3430

unable to make postgres unaccent extension working on rails

In a working app, I'm trying to implement unaccent query. 

pg-gem version 0.21
postgresql version 9.5/9.6
rails version 4

I first create/enable the extension here is the migration: 

def up
  execute 'CREATE EXTENSION IF NOT EXISTS unaccent'
end

def down
  execute 'DROP EXTENSION IF EXISTS unaccent'
end

The migration runs fine. psql \dx shows that the extension is on. A simple unccent query on psql reveals that it's working as expected.

When I try querying something like this: 

people = people.where('unaccent(people.first_name) ILIKE unaccent(?)', "%#{params[:first_name]}%") if params[:first_name].present?

I get this error: 

PG::UndefinedFunction: ERROR:  function unaccent(character varying) does not exist

At first I was thinking that the connexion was made to the wrong database as I played with a lot of versions but after assuring that was not the case, the error still remains.

To be totally sure I also test it on a test server which results with the same error.

As everything seems fine at the db level I suspect something on the rails side but I don't know how to investigate any further.

Any help/suggestion is welcome!

Upvotes: 2

Views: 5502

Answers (2)

Cristian Rennella
Cristian Rennella

Reputation: 727

Run a new migration worked for me

class AddUnaccent < ActiveRecord::Migration[6.0]
  def change
    enable_extension "unaccent"
  end
end

Then in the query

Store.where('unaccent(name) ILIKE unaccent(?)', "%#{term}%")

Upvotes: 8

Laurenz Albe
Laurenz Albe

Reputation: 247830

The extension must have been created in a schema that is not on the search_path of the rails application.

The best solution would be to have a common schema (or maybe use the existing public schema) that contains objects that are common to all schemas. Then install the extension in that schema.

Upvotes: 2

Related Questions