PostgreSQL specific lookups

Trigram similarity

The trigram_similar lookup allows you to perform trigram lookups, measuring the number of trigrams (three consecutive characters) shared, using a dedicated PostgreSQL extension. A trigram lookup is given an expression and returns results that have a similarity measurement greater than the current similarity threshold.

To use it, add 'django.contrib.postgres' in your INSTALLED_APPS and activate the pg_trgm extension on PostgreSQL. You can install the extension using the TrigramExtension migration operation.

The trigram_similar lookup can be used on CharField and TextField:

>>> City.objects.filter(name__trigram_similar="Middlesborough")
['<City: Middlesbrough>']
New in Django 4.0.

The trigram_word_similar lookup allows you to perform trigram word similarity lookups using a dedicated PostgreSQL extension. It can be approximately understood as measuring the greatest number of trigrams shared between the parameter and any substring of the field. A trigram word lookup is given an expression and returns results that have a word similarity measurement greater than the current similarity threshold.

To use it, add 'django.contrib.postgres' in your INSTALLED_APPS and activate the pg_trgm extension on PostgreSQL. You can install the extension using the TrigramExtension migration operation.

The trigram_word_similar lookup can be used on CharField and TextField:

>>> Sentence.objects.filter(name__trigram_word_similar='Middlesborough')
['<Sentence: Gumby rides on the path of Middlesbrough>']

Unaccent

The unaccent lookup allows you to perform accent-insensitive lookups using a dedicated PostgreSQL extension.

This lookup is implemented using Transform, so it can be chained with other lookup functions. To use it, you need to add 'django.contrib.postgres' in your INSTALLED_APPS and activate the unaccent extension on PostgreSQL. The UnaccentExtension migration operation is available if you want to perform this activation using migrations).

The unaccent lookup can be used on CharField and TextField:

>>> City.objects.filter(name__unaccent="México")
['<City: Mexico>']

>>> User.objects.filter(first_name__unaccent__startswith="Jerem")
['<User: Jeremy>', '<User: Jérémy>', '<User: Jérémie>', '<User: Jeremie>']

警告

unaccent lookups should perform fine in most use cases. However, queries using this filter will generally perform full table scans, which can be slow on large tables. In those cases, using dedicated full text indexing tools might be appropriate.

« previous | up | next »