HINT: No function matches the given name and argument types. You might need to add explicit type casts. TrigramSimilarity Django

Question

I am trying full text search using TrigramSimilarity. I have activated pg_trgm extension in PostgreSQL. and added django.contrib.postgres in my installed apps. But when I am trying query

blog.objects.annotate(similarity=TrigramSimilarity('name', 'abc'),).filter(similarity__gt=0.3).order_by('-similarity')

But its giving me an error

psycopg2.ProgrammingError: function similarity(character varying, unknown) does not exist LINE 1: ...others_2", "blog_mod", SIMILARITY... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts.

Why this error is happening. Please help me.

Then it seems that the pg_trgm extension has not been installed in the database. What did you do to enable it? Have you checked that it's successfully installed? — Håken Lid, Oct 19 '18 at 00:15
I get into my db using "sudo -u postgres psql" and used this command "CREATE EXTENSION pg_trgm;" — Abhimanyu Singh, Oct 19 '18 at 00:18
The error message is consistent with `pg_trgm` not being created. https://stackoverflow.com/questions/2254999/similarity-function-in-postgres-with-pg-trgm — Håken Lid, Oct 19 '18 at 00:24

score 7 · Accepted Answer · answered Oct 19 '18 at 00:36

The error indicates that the extension has not been installed. I don't know exactly why that could be. Possibly you did not install the extension in the database you use for django?

It is possible to perform raw sql queries from your django code. This will ensure that you target the correct database / schema.

from django.db import connection
with connection.cursor() as cursor:
    cursor.execute('CREATE EXTENSION IF NOT EXISTS pg_trgm')

You can also use Django's custom TrigramExtension migration. The create extension query is the same. But the migration is a good idea if you run integration tests on a test database that also needs pg_trgm to be enabled.

https://docs.djangoproject.com/en/2.1/ref/contrib/postgres/operations/#database-migration-operations

yeah it worked. I installed pg_trgm in my default database insted of the databse which django uses. Thankyou very much — Abhimanyu Singh, Oct 19 '18 at 00:43

score 2 · Answer 2 · answered Dec 20 '20 at 14:15

2

It's because (I don't know why?) TrigramSimilarity wont work on search_query. instead it will work with strings. or neither with SearchVectorField instead use regular fields.

answered Dec 20 '20 at 14:15

mh-firouzjah

834
1
6
15

score 1 · Answer 3 · edited Oct 09 '20 at 08:25

1

If the pg_trgm extension was created in the public schema you can get the error in another schema. This works for me:

select public.similarity('foo','bar');

whereas if I try Szymon's solution Postgres tells me the extension already exists. I haven't found documentation on this.

edited Oct 09 '20 at 08:25

fcdt

2,371
5
14
26

answered Oct 09 '20 at 02:39

David G.

41
1

score -1 · Answer 4 · answered May 22 '20 at 03:41

-1

I have the same issue. This would happen if you did not include the schema where TrigramSimilarity() will be used in the search path before installing this extension.

Try

SET search_path TO my_schema

Then

CREATE EXTENSION IF NOT EXISTS pg_trgm;

answered May 22 '20 at 03:41

Szymon

1
1

Magere · Answer 5 · 2020-12-20T16:47:13.553

-1

My guess is you probably have not connected to the right DB.

In Ubuntu. Try...

$ sudo su postgres

$ psql <db_name>
   <db_name> = Name of DB being used by the Django project.
   e.g psql badass_db

$ CREATE EXTENSION pg_trgm;

edited Dec 20 '20 at 16:47

answered Jul 11 '20 at 18:47

Magere

99
6

HINT: No function matches the given name and argument types. You might need to add explicit type casts. TrigramSimilarity Django

5 Answers5