[Solved]-How to have Accent-insensitive filter in django with postgres?


EDIT: Django 1.8 makes accent unsensitive lookup for postgresql builtin. https://docs.djangoproject.com/en/dev/ref/contrib/postgres/lookups/#std:fieldlookup-unaccent

In fact in postgres contrib (8.4+) there is an unaccent function to search easily:

for postgres 9/8.5:

for postgres 8.4:

here an example of usage from django:

vals = MyObject.objects.raw(
        "SELECT * \
         FROM myapp_myobject \
         WHERE unaccent(name) LIKE \'%"+search_text+"%'")

You may apply apply unaccent on text-search before comparison.

Option I made is:

#!/usr/bin/env python
# -*- coding: utf-8 -*-
# parts of credits comes to clarisys.fr
from django.db.backends.postgresql_psycopg2.base import *

class DatabaseOperations(DatabaseOperations):
    def lookup_cast(self, lookup_type):
        if lookup_type in('icontains', 'istartswith'):
            return "UPPER(unaccent(%s::text))"
            return super(DatabaseOperations, self).lookup_cast(lookup_type)

class DatabaseWrapper(DatabaseWrapper):
    def __init__(self, *args, **kwargs):
        super(DatabaseWrapper, self).__init__(*args, **kwargs)
        self.operators['icontains'] = 'LIKE UPPER(unaccent(%s))'
        self.operators['istartswith'] = 'LIKE UPPER(unaccent(%s))'
        self.ops = DatabaseOperations(self)

Use this file base.py in a folder and use this folder as db backend. icontains and istartswith are now case and accent insensitive.


I managed to install unaccent from postgresql contrib, but this answer that patches django didn’t work. load_backend on django.db.utils enforces that the backend name starts with django.db.backends.

The solution that worked for me was inserting this code in one of my modules:

from django.db.backends.postgresql_psycopg2.base import DatabaseOperations, DatabaseWrapper

def lookup_cast(self, lookup_type, internal_type=None):
    if lookup_type in('icontains', 'istartswith'):
        return "UPPER(unaccent(%s::text))"
        return super(DatabaseOperations, self).lookup_cast(lookup_type, internal_type)

def patch_unaccent():
    DatabaseOperations.lookup_cast = lookup_cast
    DatabaseWrapper.operators['icontains'] = 'LIKE UPPER(unaccent(%s))'
    DatabaseWrapper.operators['istartswith'] = 'LIKE UPPER(unaccent(%s))'
    print 'Unaccent patch'


Now unaccent searches are working fine, even inside django admin!
Thanks for your answer above!



I don’t believe you’ll be able to use the standard Django field-lookups for this unless you store a non-accented version of your text in another column and do the lookup there. You could add a duplicate column with editable=False and override the model’s save() method to update that field from the original accented text.

Python: Remove accents from unicode

PostgreSQL Wiki: Strip accents from strings, and output in lowercase



Answer from @SaeX in another thread:
How can I activate the unaccent extension on an already existing model

A migration file needs to be manually made and applied.

  1. First, create an empty migration:

./manage.py makemigrations myapp --empty

  1. Then open the file and add UnaccentExtension to operations:
from django.contrib.postgres.operations import UnaccentExtension

class Migration(migrations.Migration):

    dependencies = [

    operations = [
  1. Now apply the migration using ./manage.py migrate.

If you’d get following error during that last step:

django.db.utils.ProgrammingError: permission denied to create extension "unaccent"
HINT:  Must be superuser to create this extension.

… then temporarily allow superuser rights to your user by performing postgres# ALTER ROLE <user_name> SUPERUSER; and its NOSUPERUSER counterpart. pgAdminIII can do this, too.

Now enjoy the unaccent functionality using Django:

>>> Person.objects.filter(first_name__unaccent=u"Helène")
[<Person: Michels Hélène>]

Again, this answer belongs to @SaeX

But for me his answer still didn’t work, so don’t forget to
add the line django.contrib.postgresin INSTALLED_APPS (settings.py)


I just released (a few days ago) the django-unaccent library that add operators to the django ORM for unaccent search.
It monkeypatch the django ORM and uses the unaccent() function of postgres to do so.

Please, check this out => https://github.com/djcoin/django-unaccent



I’m working on an unaccent lookup field for django and postgreSQL. It’s on github: https://github.com/marianobianchi/django-accent-free-lookup

It’s working fine for now, but it still need a lot of work. I’m using it and it doesn’t show any problems for now.

The way of using it is to make a new Manager for the model you want to have unaccents searches (look at the example stored at the end of managers.py file at the project).

The lookups I have already implement are:





They are equivalent to the common field lookups that come with django:





with the difference that they are "accent insensitive" for the most common accented characters.

Leave a comment