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:

• https://github.com/adunstan/postgresql-dev/commits/master/contrib/unaccent
• http://www.sai.msu.su/~megera/wiki/unaccent

for postgres 8.4:

• https://launchpad.net/postgresql-unaccent

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))"
        else:
            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 fi

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))"
    else:
        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'

patch_unaccent()

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