Similarity function in Postgres with pg_trgm

Question

I'm trying to use the similarity function in Postgres to do some fuzzy text matching, however whenever I try to use it I get the error:

function similarity(character varying, unknown) does not exist

If I add explicit casts to text I get the error:

function similarity(text, text) does not exist

My query is:

SELECT (similarity("table"."field"::text, %s::text)) AS "similarity", "table".* FROM "table" WHERE similarity > .5 ORDER BY "similarity" DESC LIMIT 10

Do I need to do something to initalize pg_trgm?

Branca

Answer 1

You have to install pg_trgm. In debian, source this sql: /usr/share/postgresql/8.4/contrib/pg_trgm.sql. From the command line:

psql -f /usr/share/postgresql/8.4/contrib/pg_trgm.sql

Or inside a psql shell:

\i /usr/share/postgresql/8.4/contrib/pg_trgm.sql

The script defaults to installing in the public schema, edit the search path at the top if you want to install it somewhere else (so that uninstalling/upgrading can be done simply by dropping the schema).

Postorbital

Possibly really dumb question, but how does one do that? – Branca

Answer 2

With postgresql 9.1:

after installing (on ubuntu) sudo apt-get install postgresql-contrib as tomaszbak answered.

you just have to execute the sql command:

CREATE EXTENSION pg_trgm;

Vorster

It may be worth to remember that depending on database search_path extension could be installed in different schema than public. – Gragg

note: since you have to be superuser, go to the commandline and select your database psql --username postgres --dbname database and run the command there. Just in default database won't work for your production app – Lysippus

Answer 3

On ubuntu you need to run

sudo apt-get install postgresql-contrib

to get /usr/share/postgresql/8.4/contrib/pg_trgm.sql

Gershom

For postgres 8.4. contrib run sudo apt-get install postgresql-contrib-8.4 – Fundament

Answer 4

If you have the pg_trgm extension installed not in the public schema you must explicitly specify the schema when using the similarity function like this

select schema.similarity(foo,bar) from schema.baz

Enosis

How do I find out which schema has it installed? – Pituri

Answer 5

For Postgres 8.4 do following:

As sudo user run:

sudo apt-get install postgresql-contrib-8.4

Switch to postgres user:

sudo su - postgres

Run:

psql -U DB_USER -d DB_NAME -f /usr/share/postgresql/8.4/contrib/pg_trgm.sql

Restart postgres

Fundament

Answer 6

I was having this same issue in the context of running the Django Test Runner against a function that uses the Django 1.11 ORM for trigram similarity on Postgres 9.4.

I had to do a few things to get it working:

1) OP is correct that this required enabling the pg_trgm extension. However, in postgres9.4 this is enabled on a per-database basis. Since Django deletes and recreates the test database with each run, the new test database didn't have the extension installed. To fix this, I initialized the pg_trgm extension within the default newly-created database template in postgres. The command to do this is psql -d template1 -c 'CREATE EXTENSION pg_trgm;' run as the postgres user.

2) Postgres had to be restarted

3) The Django test runner wasn't recognizing this, so I had to upgrade from Django 1.11.12 to 1.11.18 (presumably this is also fixed in newer versions of Django)

Oolite

Answer 7

in case others are struggling too: assuming that the db is called “ttrss” I switched to the command line of postgresql:

sudo -u postgres psql --dbname=ttrss

then install the extension there:

CREATE EXTENSION pg_trgm;

Chromyl