Debian Codegraph @

docs/ref/contrib/postgres/fields.txt

raw
1 ================================
2 PostgreSQL specific model fields
3 ================================
4
5 All of these fields are available from the ``django.contrib.postgres.fields``
6 module.
7
8 .. currentmodule:: django.contrib.postgres.fields
9
10 Indexing these fields
11 =====================
12
13 :class:`~django.db.models.Index` and :attr:`.Field.db_index` both create a
14 B-tree index, which isn't particularly helpful when querying complex data types.
15 Indexes such as :class:`~django.contrib.postgres.indexes.GinIndex` and
16 :class:`~django.contrib.postgres.indexes.GistIndex` are better suited, though
17 the index choice is dependent on the queries that you're using. Generally, GiST
18 may be a good choice for the :ref:`range fields <range-fields>` and
19 :class:`HStoreField`, and GIN may be helpful for :class:`ArrayField`.
20
21 ``ArrayField``
22 ==============
23
24 .. class:: ArrayField(base_field, size=None, **options)
25
26 A field for storing lists of data. Most field types can be used, and you
27 pass another field instance as the :attr:`base_field
28 <ArrayField.base_field>`. You may also specify a :attr:`size
29 <ArrayField.size>`. ``ArrayField`` can be nested to store multi-dimensional
30 arrays.
31
32 If you give the field a :attr:`~django.db.models.Field.default`, ensure
33 it's a callable such as ``list`` (for an empty default) or a callable that
34 returns a list (such as a function). Incorrectly using ``default=[]``
35 creates a mutable default that is shared between all instances of
36 ``ArrayField``.
37
38 .. attribute:: base_field
39
40 This is a required argument.
41
42 Specifies the underlying data type and behavior for the array. It
43 should be an instance of a subclass of
44 :class:`~django.db.models.Field`. For example, it could be an
45 :class:`~django.db.models.IntegerField` or a
46 :class:`~django.db.models.CharField`. Most field types are permitted,
47 with the exception of those handling relational data
48 (:class:`~django.db.models.ForeignKey`,
49 :class:`~django.db.models.OneToOneField` and
50 :class:`~django.db.models.ManyToManyField`) and file fields
51 (:class:`~django.db.models.FileField` and
52 :class:`~django.db.models.ImageField`).
53
54 It is possible to nest array fields - you can specify an instance of
55 ``ArrayField`` as the ``base_field``. For example::
56
57 from django.contrib.postgres.fields import ArrayField
58 from django.db import models
59
60
61 class ChessBoard(models.Model):
62 board = ArrayField(
63 ArrayField(
64 models.CharField(max_length=10, blank=True),
65 size=8,
66 ),
67 size=8,
68 )
69
70 Transformation of values between the database and the model, validation
71 of data and configuration, and serialization are all delegated to the
72 underlying base field.
73
74 .. attribute:: size
75
76 This is an optional argument.
77
78 If passed, the array will have a maximum size as specified. This will
79 be passed to the database, although PostgreSQL at present does not
80 enforce the restriction.
81
82 .. note::
83
84 When nesting ``ArrayField``, whether you use the ``size`` parameter or not,
85 PostgreSQL requires that the arrays are rectangular::
86
87 from django.contrib.postgres.fields import ArrayField
88 from django.db import models
89
90
91 class Board(models.Model):
92 pieces = ArrayField(ArrayField(models.IntegerField()))
93
94
95 # Valid
96 Board(
97 pieces=[
98 [2, 3],
99 [2, 1],
100 ]
101 )
102
103 # Not valid
104 Board(
105 pieces=[
106 [2, 3],
107 [2],
108 ]
109 )
110
111 If irregular shapes are required, then the underlying field should be made
112 nullable and the values padded with ``None``.
113
114 Querying ``ArrayField``
115 -----------------------
116
117 There are a number of custom lookups and transforms for :class:`ArrayField`.
118 We will use the following example model::
119
120 from django.contrib.postgres.fields import ArrayField
121 from django.db import models
122
123
124 class Post(models.Model):
125 name = models.CharField(max_length=200)
126 tags = ArrayField(models.CharField(max_length=200), blank=True)
127
128 def __str__(self):
129 return self.name
130
131 .. fieldlookup:: arrayfield.contains
132
133 ``contains``
134 ~~~~~~~~~~~~
135
136 The :lookup:`contains` lookup is overridden on :class:`ArrayField`. The
137 returned objects will be those where the values passed are a subset of the
138 data. It uses the SQL operator ``@>``. For example:
139
140 .. code-block:: pycon
141
142 >>> Post.objects.create(name="First post", tags=["thoughts", "django"])
143 >>> Post.objects.create(name="Second post", tags=["thoughts"])
144 >>> Post.objects.create(name="Third post", tags=["tutorial", "django"])
145
146 >>> Post.objects.filter(tags__contains=["thoughts"])
147 <QuerySet [<Post: First post>, <Post: Second post>]>
148
149 >>> Post.objects.filter(tags__contains=["django"])
150 <QuerySet [<Post: First post>, <Post: Third post>]>
151
152 >>> Post.objects.filter(tags__contains=["django", "thoughts"])
153 <QuerySet [<Post: First post>]>
154
155 .. fieldlookup:: arrayfield.contained_by
156
157 ``contained_by``
158 ~~~~~~~~~~~~~~~~
159
160 This is the inverse of the :lookup:`contains <arrayfield.contains>` lookup -
161 the objects returned will be those where the data is a subset of the values
162 passed. It uses the SQL operator ``<@``. For example:
163
164 .. code-block:: pycon
165
166 >>> Post.objects.create(name="First post", tags=["thoughts", "django"])
167 >>> Post.objects.create(name="Second post", tags=["thoughts"])
168 >>> Post.objects.create(name="Third post", tags=["tutorial", "django"])
169
170 >>> Post.objects.filter(tags__contained_by=["thoughts", "django"])
171 <QuerySet [<Post: First post>, <Post: Second post>]>
172
173 >>> Post.objects.filter(tags__contained_by=["thoughts", "django", "tutorial"])
174 <QuerySet [<Post: First post>, <Post: Second post>, <Post: Third post>]>
175
176 .. fieldlookup:: arrayfield.overlap
177
178 ``overlap``
179 ~~~~~~~~~~~
180
181 Returns objects where the data shares any results with the values passed. Uses
182 the SQL operator ``&&``. For example:
183
184 .. code-block:: pycon
185
186 >>> Post.objects.create(name="First post", tags=["thoughts", "django"])
187 >>> Post.objects.create(name="Second post", tags=["thoughts", "tutorial"])
188 >>> Post.objects.create(name="Third post", tags=["tutorial", "django"])
189
190 >>> Post.objects.filter(tags__overlap=["thoughts"])
191 <QuerySet [<Post: First post>, <Post: Second post>]>
192
193 >>> Post.objects.filter(tags__overlap=["thoughts", "tutorial"])
194 <QuerySet [<Post: First post>, <Post: Second post>, <Post: Third post>]>
195
196 >>> Post.objects.filter(tags__overlap=Post.objects.values_list("tags"))
197 <QuerySet [<Post: First post>, <Post: Second post>, <Post: Third post>]>
198
199 .. fieldlookup:: arrayfield.len
200
201 ``len``
202 ~~~~~~~
203
204 Returns the length of the array. The lookups available afterward are those
205 available for :class:`~django.db.models.IntegerField`. For example:
206
207 .. code-block:: pycon
208
209 >>> Post.objects.create(name="First post", tags=["thoughts", "django"])
210 >>> Post.objects.create(name="Second post", tags=["thoughts"])
211
212 >>> Post.objects.filter(tags__len=1)
213 <QuerySet [<Post: Second post>]>
214
215 .. fieldlookup:: arrayfield.index
216
217 Index transforms
218 ~~~~~~~~~~~~~~~~
219
220 Index transforms index into the array. Any non-negative integer can be used.
221 There are no errors if it exceeds the :attr:`size <ArrayField.size>` of the
222 array. The lookups available after the transform are those from the
223 :attr:`base_field <ArrayField.base_field>`. For example:
224
225 .. code-block:: pycon
226
227 >>> Post.objects.create(name="First post", tags=["thoughts", "django"])
228 >>> Post.objects.create(name="Second post", tags=["thoughts"])
229
230 >>> Post.objects.filter(tags__0="thoughts")
231 <QuerySet [<Post: First post>, <Post: Second post>]>
232
233 >>> Post.objects.filter(tags__1__iexact="Django")
234 <QuerySet [<Post: First post>]>
235
236 >>> Post.objects.filter(tags__276="javascript")
237 <QuerySet []>
238
239 .. note::
240
241 PostgreSQL uses 1-based indexing for array fields when writing raw SQL.
242 However these indexes and those used in :lookup:`slices <arrayfield.slice>`
243 use 0-based indexing to be consistent with Python.
244
245 .. fieldlookup:: arrayfield.slice
246
247 Slice transforms
248 ~~~~~~~~~~~~~~~~
249
250 Slice transforms take a slice of the array. Any two non-negative integers can
251 be used, separated by a single underscore. The lookups available after the
252 transform do not change. For example:
253
254 .. code-block:: pycon
255
256 >>> Post.objects.create(name="First post", tags=["thoughts", "django"])
257 >>> Post.objects.create(name="Second post", tags=["thoughts"])
258 >>> Post.objects.create(name="Third post", tags=["django", "python", "thoughts"])
259
260 >>> Post.objects.filter(tags__0_1=["thoughts"])
261 <QuerySet [<Post: First post>, <Post: Second post>]>
262
263 >>> Post.objects.filter(tags__0_2__contains=["thoughts"])
264 <QuerySet [<Post: First post>, <Post: Second post>]>
265
266 .. note::
267
268 PostgreSQL uses 1-based indexing for array fields when writing raw SQL.
269 However these slices and those used in :lookup:`indexes <arrayfield.index>`
270 use 0-based indexing to be consistent with Python.
271
272 .. admonition:: Multidimensional arrays with indexes and slices
273
274 PostgreSQL has some rather esoteric behavior when using indexes and slices
275 on multidimensional arrays. It will always work to use indexes to reach
276 down to the final underlying data, but most other slices behave strangely
277 at the database level and cannot be supported in a logical, consistent
278 fashion by Django.
279
280 ``HStoreField``
281 ===============
282
283 .. class:: HStoreField(**options)
284
285 A field for storing key-value pairs. The Python data type used is a
286 ``dict``. Keys must be strings, and values may be either strings or nulls
287 (``None`` in Python).
288
289 To use this field, you'll need to:
290
291 #. Add ``'django.contrib.postgres'`` in your :setting:`INSTALLED_APPS`.
292 #. :ref:`Set up the hstore extension <create-postgresql-extensions>` in
293 PostgreSQL.
294
295 You'll see an error like ``can't adapt type 'dict'`` if you skip the first
296 step, or ``type "hstore" does not exist`` if you skip the second.
297
298 .. note::
299
300 On occasions it may be useful to require or restrict the keys which are
301 valid for a given field. This can be done using the
302 :class:`~django.contrib.postgres.validators.KeysValidator`.
303
304 Querying ``HStoreField``
305 ------------------------
306
307 In addition to the ability to query by key, there are a number of custom
308 lookups available for ``HStoreField``.
309
310 We will use the following example model::
311
312 from django.contrib.postgres.fields import HStoreField
313 from django.db import models
314
315
316 class Dog(models.Model):
317 name = models.CharField(max_length=200)
318 data = HStoreField()
319
320 def __str__(self):
321 return self.name
322
323 .. fieldlookup:: hstorefield.key
324
325 Key lookups
326 ~~~~~~~~~~~
327
328 To query based on a given key, you can use that key as the lookup name:
329
330 .. code-block:: pycon
331
332 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador"})
333 >>> Dog.objects.create(name="Meg", data={"breed": "collie"})
334
335 >>> Dog.objects.filter(data__breed="collie")
336 <QuerySet [<Dog: Meg>]>
337
338 You can chain other lookups after key lookups:
339
340 .. code-block:: pycon
341
342 >>> Dog.objects.filter(data__breed__contains="l")
343 <QuerySet [<Dog: Rufus>, <Dog: Meg>]>
344
345 or use ``F()`` expressions to annotate a key value. For example:
346
347 .. code-block:: pycon
348
349 >>> from django.db.models import F
350 >>> rufus = Dog.objects.annotate(breed=F("data__breed"))[0]
351 >>> rufus.breed
352 'labrador'
353
354 If the key you wish to query by clashes with the name of another lookup, you
355 need to use the :lookup:`hstorefield.contains` lookup instead.
356
357 .. note::
358
359 Key transforms can also be chained with: :lookup:`contains`,
360 :lookup:`icontains`, :lookup:`endswith`, :lookup:`iendswith`,
361 :lookup:`iexact`, :lookup:`regex`, :lookup:`iregex`, :lookup:`startswith`,
362 and :lookup:`istartswith` lookups.
363
364 .. warning::
365
366 Since any string could be a key in a hstore value, any lookup other than
367 those listed below will be interpreted as a key lookup. No errors are
368 raised. Be extra careful for typing mistakes, and always check your queries
369 work as you intend.
370
371 .. fieldlookup:: hstorefield.contains
372
373 ``contains``
374 ~~~~~~~~~~~~
375
376 The :lookup:`contains` lookup is overridden on
377 :class:`~django.contrib.postgres.fields.HStoreField`. The returned objects are
378 those where the given ``dict`` of key-value pairs are all contained in the
379 field. It uses the SQL operator ``@>``. For example:
380
381 .. code-block:: pycon
382
383 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador", "owner": "Bob"})
384 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
385 >>> Dog.objects.create(name="Fred", data={})
386
387 >>> Dog.objects.filter(data__contains={"owner": "Bob"})
388 <QuerySet [<Dog: Rufus>, <Dog: Meg>]>
389
390 >>> Dog.objects.filter(data__contains={"breed": "collie"})
391 <QuerySet [<Dog: Meg>]>
392
393 .. fieldlookup:: hstorefield.contained_by
394
395 ``contained_by``
396 ~~~~~~~~~~~~~~~~
397
398 This is the inverse of the :lookup:`contains <hstorefield.contains>` lookup -
399 the objects returned will be those where the key-value pairs on the object are
400 a subset of those in the value passed. It uses the SQL operator ``<@``. For
401 example:
402
403 .. code-block:: pycon
404
405 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador", "owner": "Bob"})
406 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
407 >>> Dog.objects.create(name="Fred", data={})
408
409 >>> Dog.objects.filter(data__contained_by={"breed": "collie", "owner": "Bob"})
410 <QuerySet [<Dog: Meg>, <Dog: Fred>]>
411
412 >>> Dog.objects.filter(data__contained_by={"breed": "collie"})
413 <QuerySet [<Dog: Fred>]>
414
415 .. fieldlookup:: hstorefield.has_key
416
417 ``has_key``
418 ~~~~~~~~~~~
419
420 Returns objects where the given key is in the data. Uses the SQL operator
421 ``?``. For example:
422
423 .. code-block:: pycon
424
425 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador"})
426 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
427
428 >>> Dog.objects.filter(data__has_key="owner")
429 <QuerySet [<Dog: Meg>]>
430
431 .. fieldlookup:: hstorefield.has_any_keys
432
433 ``has_any_keys``
434 ~~~~~~~~~~~~~~~~
435
436 Returns objects where any of the given keys are in the data. Uses the SQL
437 operator ``?|``. For example:
438
439 .. code-block:: pycon
440
441 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador"})
442 >>> Dog.objects.create(name="Meg", data={"owner": "Bob"})
443 >>> Dog.objects.create(name="Fred", data={})
444
445 >>> Dog.objects.filter(data__has_any_keys=["owner", "breed"])
446 <QuerySet [<Dog: Rufus>, <Dog: Meg>]>
447
448 .. fieldlookup:: hstorefield.has_keys
449
450 ``has_keys``
451 ~~~~~~~~~~~~
452
453 Returns objects where all of the given keys are in the data. Uses the SQL operator
454 ``?&``. For example:
455
456 .. code-block:: pycon
457
458 >>> Dog.objects.create(name="Rufus", data={})
459 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
460
461 >>> Dog.objects.filter(data__has_keys=["breed", "owner"])
462 <QuerySet [<Dog: Meg>]>
463
464 .. fieldlookup:: hstorefield.keys
465
466 ``keys``
467 ~~~~~~~~
468
469 Returns objects where the array of keys is the given value. Note that the order
470 is not guaranteed to be reliable, so this transform is mainly useful for using
471 in conjunction with lookups on
472 :class:`~django.contrib.postgres.fields.ArrayField`. Uses the SQL function
473 ``akeys()``. For example:
474
475 .. code-block:: pycon
476
477 >>> Dog.objects.create(name="Rufus", data={"toy": "bone"})
478 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
479
480 >>> Dog.objects.filter(data__keys__overlap=["breed", "toy"])
481 <QuerySet [<Dog: Rufus>, <Dog: Meg>]>
482
483 .. fieldlookup:: hstorefield.values
484
485 ``values``
486 ~~~~~~~~~~
487
488 Returns objects where the array of values is the given value. Note that the
489 order is not guaranteed to be reliable, so this transform is mainly useful for
490 using in conjunction with lookups on
491 :class:`~django.contrib.postgres.fields.ArrayField`. Uses the SQL function
492 ``avals()``. For example:
493
494 .. code-block:: pycon
495
496 >>> Dog.objects.create(name="Rufus", data={"breed": "labrador"})
497 >>> Dog.objects.create(name="Meg", data={"breed": "collie", "owner": "Bob"})
498
499 >>> Dog.objects.filter(data__values__contains=["collie"])
500 <QuerySet [<Dog: Meg>]>
501
502 .. _range-fields:
503
504 Range Fields
505 ============
506
507 There are five range field types, corresponding to the built-in range types in
508 PostgreSQL. These fields are used to store a range of values; for example the
509 start and end timestamps of an event, or the range of ages an activity is
510 suitable for.
511
512 All of the range fields translate to :ref:`psycopg Range objects
513 <psycopg:adapt-range>` in Python, but also accept tuples as input if no bounds
514 information is necessary. The default is lower bound included, upper bound
515 excluded, that is ``[)`` (see the PostgreSQL documentation for details about
516 `different bounds`_). The default bounds can be changed for non-discrete range
517 fields (:class:`.DateTimeRangeField` and :class:`.DecimalRangeField`) by using
518 the ``default_bounds`` argument.
519
520 .. admonition:: PostgreSQL normalizes a range with no points to the empty range
521
522 A range with equal values specified for an included lower bound and an
523 excluded upper bound, such as ``Range(datetime.date(2005, 6, 21),
524 datetime.date(2005, 6, 21))`` or ``[4, 4)``, has no points. PostgreSQL will
525 normalize the value to empty when saving to the database, and the original
526 bound values will be lost. See the `PostgreSQL documentation for details
527 <https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-IO>`_.
528
529 ``IntegerRangeField``
530 ---------------------
531
532 .. class:: IntegerRangeField(**options)
533
534 Stores a range of integers. Based on an
535 :class:`~django.db.models.IntegerField`. Represented by an ``int4range`` in
536 the database and a
537 ``django.db.backends.postgresql.psycopg_any.NumericRange`` in Python.
538
539 Regardless of the bounds specified when saving the data, PostgreSQL always
540 returns a range in a canonical form that includes the lower bound and
541 excludes the upper bound, that is ``[)``.
542
543 ``BigIntegerRangeField``
544 ------------------------
545
546 .. class:: BigIntegerRangeField(**options)
547
548 Stores a range of large integers. Based on a
549 :class:`~django.db.models.BigIntegerField`. Represented by an ``int8range``
550 in the database and a
551 ``django.db.backends.postgresql.psycopg_any.NumericRange`` in Python.
552
553 Regardless of the bounds specified when saving the data, PostgreSQL always
554 returns a range in a canonical form that includes the lower bound and
555 excludes the upper bound, that is ``[)``.
556
557 ``DecimalRangeField``
558 ---------------------
559
560 .. class:: DecimalRangeField(default_bounds='[)', **options)
561
562 Stores a range of floating point values. Based on a
563 :class:`~django.db.models.DecimalField`. Represented by a ``numrange`` in
564 the database and a
565 ``django.db.backends.postgresql.psycopg_any.NumericRange`` in Python.
566
567 .. attribute:: DecimalRangeField.default_bounds
568
569 Optional. The value of ``bounds`` for list and tuple inputs. The
570 default is lower bound included, upper bound excluded, that is ``[)``
571 (see the PostgreSQL documentation for details about
572 `different bounds`_). ``default_bounds`` is not used for
573 ``django.db.backends.postgresql.psycopg_any.NumericRange`` inputs.
574
575 ``DateTimeRangeField``
576 ----------------------
577
578 .. class:: DateTimeRangeField(default_bounds='[)', **options)
579
580 Stores a range of timestamps. Based on a
581 :class:`~django.db.models.DateTimeField`. Represented by a ``tstzrange`` in
582 the database and a
583 ``django.db.backends.postgresql.psycopg_any.DateTimeTZRange`` in Python.
584
585 .. attribute:: DateTimeRangeField.default_bounds
586
587 Optional. The value of ``bounds`` for list and tuple inputs. The
588 default is lower bound included, upper bound excluded, that is ``[)``
589 (see the PostgreSQL documentation for details about
590 `different bounds`_). ``default_bounds`` is not used for
591 ``django.db.backends.postgresql.psycopg_any.DateTimeTZRange`` inputs.
592
593 ``DateRangeField``
594 ------------------
595
596 .. class:: DateRangeField(**options)
597
598 Stores a range of dates. Based on a
599 :class:`~django.db.models.DateField`. Represented by a ``daterange`` in the
600 database and a ``django.db.backends.postgresql.psycopg_any.DateRange`` in
601 Python.
602
603 Regardless of the bounds specified when saving the data, PostgreSQL always
604 returns a range in a canonical form that includes the lower bound and
605 excludes the upper bound, that is ``[)``.
606
607 Querying Range Fields
608 ---------------------
609
610 There are a number of custom lookups and transforms for range fields. They are
611 available on all the above fields, but we will use the following example
612 model::
613
614 from django.contrib.postgres.fields import IntegerRangeField
615 from django.db import models
616
617
618 class Event(models.Model):
619 name = models.CharField(max_length=200)
620 ages = IntegerRangeField()
621 start = models.DateTimeField()
622
623 def __str__(self):
624 return self.name
625
626 We will also use the following example objects:
627
628 .. code-block:: pycon
629
630 >>> import datetime
631 >>> from django.utils import timezone
632 >>> now = timezone.now()
633 >>> Event.objects.create(name="Soft play", ages=(0, 10), start=now)
634 >>> Event.objects.create(
635 ... name="Pub trip", ages=(21, None), start=now - datetime.timedelta(days=1)
636 ... )
637
638 and ``NumericRange``:
639
640 >>> from django.db.backends.postgresql.psycopg_any import NumericRange
641
642 Containment functions
643 ~~~~~~~~~~~~~~~~~~~~~
644
645 As with other PostgreSQL fields, there are three standard containment
646 operators: ``contains``, ``contained_by`` and ``overlap``, using the SQL
647 operators ``@>``, ``<@``, and ``&&`` respectively.
648
649 .. fieldlookup:: rangefield.contains
650
651 ``contains``
652 ^^^^^^^^^^^^
653
654 >>> Event.objects.filter(ages__contains=NumericRange(4, 5))
655 <QuerySet [<Event: Soft play>]>
656
657 .. fieldlookup:: rangefield.contained_by
658
659 ``contained_by``
660 ^^^^^^^^^^^^^^^^
661
662 >>> Event.objects.filter(ages__contained_by=NumericRange(0, 15))
663 <QuerySet [<Event: Soft play>]>
664
665 The ``contained_by`` lookup is also available on the non-range field types:
666 :class:`~django.db.models.SmallAutoField`,
667 :class:`~django.db.models.AutoField`, :class:`~django.db.models.BigAutoField`,
668 :class:`~django.db.models.SmallIntegerField`,
669 :class:`~django.db.models.IntegerField`,
670 :class:`~django.db.models.BigIntegerField`,
671 :class:`~django.db.models.DecimalField`, :class:`~django.db.models.FloatField`,
672 :class:`~django.db.models.DateField`, and
673 :class:`~django.db.models.DateTimeField`. For example:
674
675 .. code-block:: pycon
676
677 >>> from django.db.backends.postgresql.psycopg_any import DateTimeTZRange
678 >>> Event.objects.filter(
679 ... start__contained_by=DateTimeTZRange(
680 ... timezone.now() - datetime.timedelta(hours=1),
681 ... timezone.now() + datetime.timedelta(hours=1),
682 ... ),
683 ... )
684 <QuerySet [<Event: Soft play>]>
685
686 .. fieldlookup:: rangefield.overlap
687
688 ``overlap``
689 ^^^^^^^^^^^
690
691 >>> Event.objects.filter(ages__overlap=NumericRange(8, 12))
692 <QuerySet [<Event: Soft play>]>
693
694 Comparison functions
695 ~~~~~~~~~~~~~~~~~~~~
696
697 Range fields support the standard lookups: :lookup:`lt`, :lookup:`gt`,
698 :lookup:`lte` and :lookup:`gte`. These are not particularly helpful - they
699 compare the lower bounds first and then the upper bounds only if necessary.
700 This is also the strategy used to order by a range field. It is better to use
701 the specific range comparison operators.
702
703 .. fieldlookup:: rangefield.fully_lt
704
705 ``fully_lt``
706 ^^^^^^^^^^^^
707
708 The returned ranges are strictly less than the passed range. In other words,
709 all the points in the returned range are less than all those in the passed
710 range.
711
712 >>> Event.objects.filter(ages__fully_lt=NumericRange(11, 15))
713 <QuerySet [<Event: Soft play>]>
714
715 .. fieldlookup:: rangefield.fully_gt
716
717 ``fully_gt``
718 ^^^^^^^^^^^^
719
720 The returned ranges are strictly greater than the passed range. In other words,
721 the all the points in the returned range are greater than all those in the
722 passed range.
723
724 >>> Event.objects.filter(ages__fully_gt=NumericRange(11, 15))
725 <QuerySet [<Event: Pub trip>]>
726
727 .. fieldlookup:: rangefield.not_lt
728
729 ``not_lt``
730 ^^^^^^^^^^
731
732 The returned ranges do not contain any points less than the passed range, that
733 is the lower bound of the returned range is at least the lower bound of the
734 passed range.
735
736 >>> Event.objects.filter(ages__not_lt=NumericRange(0, 15))
737 <QuerySet [<Event: Soft play>, <Event: Pub trip>]>
738
739 .. fieldlookup:: rangefield.not_gt
740
741 ``not_gt``
742 ^^^^^^^^^^
743
744 The returned ranges do not contain any points greater than the passed range, that
745 is the upper bound of the returned range is at most the upper bound of the
746 passed range.
747
748 >>> Event.objects.filter(ages__not_gt=NumericRange(3, 10))
749 <QuerySet [<Event: Soft play>]>
750
751 .. fieldlookup:: rangefield.adjacent_to
752
753 ``adjacent_to``
754 ^^^^^^^^^^^^^^^
755
756 The returned ranges share a bound with the passed range.
757
758 >>> Event.objects.filter(ages__adjacent_to=NumericRange(10, 21))
759 <QuerySet [<Event: Soft play>, <Event: Pub trip>]>
760
761 Querying using the bounds
762 ~~~~~~~~~~~~~~~~~~~~~~~~~
763
764 Range fields support several extra lookups.
765
766 .. fieldlookup:: rangefield.startswith
767
768 ``startswith``
769 ^^^^^^^^^^^^^^
770
771 Returned objects have the given lower bound. Can be chained to valid lookups
772 for the base field.
773
774 >>> Event.objects.filter(ages__startswith=21)
775 <QuerySet [<Event: Pub trip>]>
776
777 .. fieldlookup:: rangefield.endswith
778
779 ``endswith``
780 ^^^^^^^^^^^^
781
782 Returned objects have the given upper bound. Can be chained to valid lookups
783 for the base field.
784
785 >>> Event.objects.filter(ages__endswith=10)
786 <QuerySet [<Event: Soft play>]>
787
788 .. fieldlookup:: rangefield.isempty
789
790 ``isempty``
791 ^^^^^^^^^^^
792
793 Returned objects are empty ranges. Can be chained to valid lookups for a
794 :class:`~django.db.models.BooleanField`.
795
796 >>> Event.objects.filter(ages__isempty=True)
797 <QuerySet []>
798
799 .. fieldlookup:: rangefield.lower_inc
800
801 ``lower_inc``
802 ^^^^^^^^^^^^^
803
804 Returns objects that have inclusive or exclusive lower bounds, depending on the
805 boolean value passed. Can be chained to valid lookups for a
806 :class:`~django.db.models.BooleanField`.
807
808 >>> Event.objects.filter(ages__lower_inc=True)
809 <QuerySet [<Event: Soft play>, <Event: Pub trip>]>
810
811 .. fieldlookup:: rangefield.lower_inf
812
813 ``lower_inf``
814 ^^^^^^^^^^^^^
815
816 Returns objects that have unbounded (infinite) or bounded lower bound,
817 depending on the boolean value passed. Can be chained to valid lookups for a
818 :class:`~django.db.models.BooleanField`.
819
820 >>> Event.objects.filter(ages__lower_inf=True)
821 <QuerySet []>
822
823 .. fieldlookup:: rangefield.upper_inc
824
825 ``upper_inc``
826 ^^^^^^^^^^^^^
827
828 Returns objects that have inclusive or exclusive upper bounds, depending on the
829 boolean value passed. Can be chained to valid lookups for a
830 :class:`~django.db.models.BooleanField`.
831
832 >>> Event.objects.filter(ages__upper_inc=True)
833 <QuerySet []>
834
835 .. fieldlookup:: rangefield.upper_inf
836
837 ``upper_inf``
838 ^^^^^^^^^^^^^
839
840 Returns objects that have unbounded (infinite) or bounded upper bound,
841 depending on the boolean value passed. Can be chained to valid lookups for a
842 :class:`~django.db.models.BooleanField`.
843
844 >>> Event.objects.filter(ages__upper_inf=True)
845 <QuerySet [<Event: Pub trip>]>
846
847 Defining your own range types
848 -----------------------------
849
850 PostgreSQL allows the definition of custom range types. Django's model and form
851 field implementations use base classes below, and ``psycopg`` provides a
852 :func:`~psycopg:psycopg.types.range.register_range` to allow use of custom
853 range types.
854
855 .. class:: RangeField(**options)
856
857 Base class for model range fields.
858
859 .. attribute:: base_field
860
861 The model field class to use.
862
863 .. attribute:: range_type
864
865 The range type to use.
866
867 .. attribute:: form_field
868
869 The form field class to use. Should be a subclass of
870 :class:`django.contrib.postgres.forms.BaseRangeField`.
871
872 .. class:: django.contrib.postgres.forms.BaseRangeField
873
874 Base class for form range fields.
875
876 .. attribute:: base_field
877
878 The form field to use.
879
880 .. attribute:: range_type
881
882 The range type to use.
883
884 Range operators
885 ---------------
886
887 .. class:: RangeOperators
888
889 PostgreSQL provides a set of SQL operators that can be used together with the
890 range data types (see `the PostgreSQL documentation for the full details of
891 range operators <https://www.postgresql.org/docs/current/
892 functions-range.html#RANGE-OPERATORS-TABLE>`_). This class is meant as a
893 convenient method to avoid typos. The operator names overlap with the names of
894 corresponding lookups.
895
896 .. code-block:: python
897
898 class RangeOperators:
899 EQUAL = "="
900 NOT_EQUAL = "<>"
901 CONTAINS = "@>"
902 CONTAINED_BY = "<@"
903 OVERLAPS = "&&"
904 FULLY_LT = "<<"
905 FULLY_GT = ">>"
906 NOT_LT = "&>"
907 NOT_GT = "&<"
908 ADJACENT_TO = "-|-"
909
910 RangeBoundary() expressions
911 ---------------------------
912
913 .. class:: RangeBoundary(inclusive_lower=True, inclusive_upper=False)
914
915 .. attribute:: inclusive_lower
916
917 If ``True`` (default), the lower bound is inclusive ``'['``, otherwise
918 it's exclusive ``'('``.
919
920 .. attribute:: inclusive_upper
921
922 If ``False`` (default), the upper bound is exclusive ``')'``, otherwise
923 it's inclusive ``']'``.
924
925 A ``RangeBoundary()`` expression represents the range boundaries. It can be
926 used with a custom range functions that expected boundaries, for example to
927 define :class:`~django.contrib.postgres.constraints.ExclusionConstraint`. See
928 `the PostgreSQL documentation for the full details <https://www.postgresql.org/
929 docs/current/rangetypes.html#RANGETYPES-INCLUSIVITY>`_.
930
931 .. _different bounds: https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-IO