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.

Answer 1

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