| 1 |
=========================== |
| 2 |
PostgreSQL specific lookups |
| 3 |
=========================== |
| 4 |
|
| 5 |
Trigram similarity |
| 6 |
================== |
| 7 |
|
| 8 |
.. fieldlookup:: trigram_similar |
| 9 |
|
| 10 |
``trigram_similar`` |
| 11 |
------------------- |
| 12 |
|
| 13 |
The ``trigram_similar`` lookup allows you to perform trigram lookups, |
| 14 |
measuring the number of trigrams (three consecutive characters) shared, using a |
| 15 |
dedicated PostgreSQL extension. A trigram lookup is given an expression and |
| 16 |
returns results that have a similarity measurement greater than the current |
| 17 |
similarity threshold. |
| 18 |
|
| 19 |
To use it, add ``'django.contrib.postgres'`` in your :setting:`INSTALLED_APPS` |
| 20 |
and activate the `pg_trgm extension`_ on PostgreSQL. You can install the |
| 21 |
extension using the |
| 22 |
:class:`~django.contrib.postgres.operations.TrigramExtension` migration |
| 23 |
operation. |
| 24 |
|
| 25 |
The ``trigram_similar`` lookup can be used on |
| 26 |
:class:`~django.db.models.CharField` and :class:`~django.db.models.TextField`: |
| 27 |
|
| 28 |
.. code-block:: pycon |
| 29 |
|
| 30 |
>>> City.objects.filter(name__trigram_similar="Middlesborough") |
| 31 |
['<City: Middlesbrough>'] |
| 32 |
|
| 33 |
.. fieldlookup:: trigram_word_similar |
| 34 |
|
| 35 |
``trigram_word_similar`` |
| 36 |
------------------------ |
| 37 |
|
| 38 |
The ``trigram_word_similar`` lookup allows you to perform trigram word |
| 39 |
similarity lookups using a dedicated PostgreSQL extension. It can be |
| 40 |
approximately understood as measuring the greatest number of trigrams shared |
| 41 |
between the parameter and any substring of the field. A trigram word lookup is |
| 42 |
given an expression and returns results that have a word similarity measurement |
| 43 |
greater than the current similarity threshold. |
| 44 |
|
| 45 |
To use it, add ``'django.contrib.postgres'`` in your :setting:`INSTALLED_APPS` |
| 46 |
and activate the `pg_trgm extension`_ on PostgreSQL. You can install the |
| 47 |
extension using the |
| 48 |
:class:`~django.contrib.postgres.operations.TrigramExtension` migration |
| 49 |
operation. |
| 50 |
|
| 51 |
The ``trigram_word_similar`` lookup can be used on |
| 52 |
:class:`~django.db.models.CharField` and :class:`~django.db.models.TextField`: |
| 53 |
|
| 54 |
.. code-block:: pycon |
| 55 |
|
| 56 |
>>> Sentence.objects.filter(name__trigram_word_similar="Middlesborough") |
| 57 |
['<Sentence: Gumby rides on the path of Middlesbrough>'] |
| 58 |
|
| 59 |
.. fieldlookup:: trigram_strict_word_similar |
| 60 |
|
| 61 |
``trigram_strict_word_similar`` |
| 62 |
------------------------------- |
| 63 |
|
| 64 |
Similar to :lookup:`trigram_word_similar`, except that it forces extent |
| 65 |
boundaries to match word boundaries. |
| 66 |
|
| 67 |
To use it, add ``'django.contrib.postgres'`` in your :setting:`INSTALLED_APPS` |
| 68 |
and activate the `pg_trgm extension`_ on PostgreSQL. You can install the |
| 69 |
extension using the |
| 70 |
:class:`~django.contrib.postgres.operations.TrigramExtension` migration |
| 71 |
operation. |
| 72 |
|
| 73 |
The ``trigram_strict_word_similar`` lookup can be used on |
| 74 |
:class:`~django.db.models.CharField` and :class:`~django.db.models.TextField`. |
| 75 |
|
| 76 |
.. _`pg_trgm extension`: https://www.postgresql.org/docs/current/pgtrgm.html |
| 77 |
|
| 78 |
``Unaccent`` |
| 79 |
============ |
| 80 |
|
| 81 |
.. fieldlookup:: unaccent |
| 82 |
|
| 83 |
The ``unaccent`` lookup allows you to perform accent-insensitive lookups using |
| 84 |
a dedicated PostgreSQL extension. |
| 85 |
|
| 86 |
This lookup is implemented using :class:`~django.db.models.Transform`, so it |
| 87 |
can be chained with other lookup functions. To use it, you need to add |
| 88 |
``'django.contrib.postgres'`` in your :setting:`INSTALLED_APPS` and activate |
| 89 |
the `unaccent extension on PostgreSQL`_. The |
| 90 |
:class:`~django.contrib.postgres.operations.UnaccentExtension` migration |
| 91 |
operation is available if you want to perform this activation using migrations). |
| 92 |
|
| 93 |
.. _unaccent extension on PostgreSQL: https://www.postgresql.org/docs/current/unaccent.html |
| 94 |
|
| 95 |
The ``unaccent`` lookup can be used on |
| 96 |
:class:`~django.db.models.CharField` and :class:`~django.db.models.TextField`: |
| 97 |
|
| 98 |
.. code-block:: pycon |
| 99 |
|
| 100 |
>>> City.objects.filter(name__unaccent="México") |
| 101 |
['<City: Mexico>'] |
| 102 |
|
| 103 |
>>> User.objects.filter(first_name__unaccent__startswith="Jerem") |
| 104 |
['<User: Jeremy>', '<User: Jérémy>', '<User: Jérémie>', '<User: Jeremie>'] |
| 105 |
|
| 106 |
.. warning:: |
| 107 |
|
| 108 |
``unaccent`` lookups should perform fine in most use cases. However, queries |
| 109 |
using this filter will generally perform full table scans, which can be slow |
| 110 |
on large tables. In those cases, using dedicated full text indexing tools |
| 111 |
might be appropriate. |