CODEWITHSUNDEEP
ruby-on-railspostgresqlheroku
Constant Meiring
Constant Meiring

Reputation: 3325

How to enable contrib modules on Heroku Postgres database

I'm trying to use contrib modules in the new Postgres 9 shared databases on Heroku. More specifically, the pg_trgm and fuzzystrmatch modules. In the documentation it says

In addition, many complimentary extensions are available such as fuzzystrmatch, pg_trgm, and unaccent.

I can't seem to find any documentation on HOW to actually enable these modules on a shared Heroku database. See answer below.

NOTE:

I tried adding them by connecting to the database with

heroku pg:psql HEROKU_POSTGRESQL_BROWN

and running

create extension pg_trgm
create extension fuzzystrmatch

but after trying to use it with

SELECT levenshtein('tests', 'test');

it still said

ERROR:  function levenshtein(unknown, unknown) does not existLINE 1: SELECT levenshtein('tests', 'test');
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

Anybody know why this happens?

Upvotes: 13

Views: 4089

Answers (2)

Brian Hempel
Brian Hempel

Reputation: 9094

In newer versions of Rails it should be sufficient to do:

def change
  enable_extension "fuzzystrmatch"
  enable_extension "pg_trgm"
end

If you need to write up and down methods, the corresponding method to enable_extension is disable_extension.

Upvotes: 7

Constant Meiring
Constant Meiring

Reputation: 3325

Found answer here while scouring Stack Overflow. Don't know why it didn't come up in any of my Google searches. Going to leave the question here if anybody else uses the same wording to search for this.

To enable modules, you need to add them to a migration as follows:

def up
  execute "create extension fuzzystrmatch"
  execute "create extension pg_trgm"
end

Upvotes: 17

Related Questions