Django ORM's "unaccent" database lookup for Postgres
Since version 1.8, Django comes with
django.contrib.postgres, an app that provides extensions for PostgreSQL specific functionality. An since then, Django supports
They work similar to the familar case-insensitive lookups on
Person.objects.filter(name__iexact="paul") ['<Person: Paul>'] Person.objects.filter(name__iexact="PAUL") ['<Person: Paul>']
But, what if you are looking for José or Jérémy, and you can't rely on correct use of accents, either in the lookup string or the database entries?
unaccent lookup, you can perform a database lookup insensitive to accents.
Person.objects.filter(name__unaccent="Jeremy") ['<Person: Jérémy>', '<Person: Jéremy>', '<Person: Jeremy>'] Person.objects.filter(name__unaccent="Jose") ['<Person: José>', '<Person: Jose>']
Install Django unaccent
unaccent functionality is part of the
postgres app that was added to Django's contrib package, starting with Django 1.8 LTS. Hence, you need to load it the same way as you load any other app in Django, In
settings.py, add the app
django.contrib.postgres to the
INSTALLED_APPS = [ ... 'django.contrib.postgres', ... ]
Limitations of Django unaccent
Django's unaccent uses the
Transform() API and therefore does a full table scan to execute the lookup. That can be very slow for large databases, so its not always a good solution.
For larger datasets, it is still better to maintain an
indexed "ASCII-only" copy of the data column to perfom the lookup, using a string with all accents removed.
See Django docs here.