Debian Codegraph @

docs/ref/contrib/postgres/lookups.txt

raw
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.