how to have Accent-insensitive filter in django with postgres?

Question

Hi I find that on postgres database, we can't configure default accent sensivity (on old mail exchanges).

Is there a way to have a _icontains also insensitive to special caracters (é, è, à, ç, ï) or I must use postgres regex to replace both side with _iregex (ç->c, é->e ...)?

edit: this question is old, and is kept for users of django before 1.8. For those using latest django versions, here the new way: https://docs.djangoproject.com/en/dev/ref/contrib/postgres/lookups/#std:fieldlookup-unaccent

Answer 1

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 file base.py in a folder and use this folder as db backend. icontains and istartswith are now case and accent insensitive.

Answer 2

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!

Answer 3

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 4

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

2. Then open the file and add UnaccentExtension to operations:

from django.contrib.postgres.operations import UnaccentExtension


class Migration(migrations.Migration):

    dependencies = [
        (<snip>)
    ]

    operations = [
        UnaccentExtension()
    ]

3. 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)

Answer 5

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

Answer 6

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:

"__aexact"

"__aiexact"

"__acontains"

"__aicontains"

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

"__exact"

"__iexact"

"__contains"

"__icontains"

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