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