diff options
Diffstat (limited to 'docs/ref/contrib/postgres')
| -rw-r--r-- | docs/ref/contrib/postgres/constraints.txt | 151 | ||||
| -rw-r--r-- | docs/ref/contrib/postgres/fields.txt | 23 | ||||
| -rw-r--r-- | docs/ref/contrib/postgres/index.txt | 1 |
3 files changed, 175 insertions, 0 deletions
diff --git a/docs/ref/contrib/postgres/constraints.txt b/docs/ref/contrib/postgres/constraints.txt new file mode 100644 index 0000000000..fe9e72e605 --- /dev/null +++ b/docs/ref/contrib/postgres/constraints.txt @@ -0,0 +1,151 @@ +======================================== +PostgreSQL specific database constraints +======================================== + +.. module:: django.contrib.postgres.constraints + :synopsis: PostgreSQL specific database constraint + +PostgreSQL supports additional data integrity constraints available from the +``django.contrib.postgres.constraints`` module. They are added in the model +:attr:`Meta.constraints <django.db.models.Options.constraints>` option. + +``ExclusionConstraint`` +======================= + +.. versionadded:: 3.0 + +.. class:: ExclusionConstraint(*, name, expressions, index_type=None, condition=None) + + Creates an exclusion constraint in the database. Internally, PostgreSQL + implements exclusion constraints using indexes. The default index type is + `GiST <https://www.postgresql.org/docs/current/gist.html>`_. To use them, + you need to activate the `btree_gist extension + <https://www.postgresql.org/docs/current/btree-gist.html>`_ on PostgreSQL. + You can install it using the + :class:`~django.contrib.postgres.operations.BtreeGistExtension` migration + operation. + + If you attempt to insert a new row that conflicts with an existing row, an + :exc:`~django.db.IntegrityError` is raised. Similarly, when update + conflicts with an existing row. + +``name`` +-------- + +.. attribute:: ExclusionConstraint.name + +The name of the constraint. + +``expressions`` +--------------- + +.. attribute:: ExclusionConstraint.expressions + +An iterable of 2-tuples. The first element is an expression or string. The +second element is a SQL operator represented as a string. To avoid typos, you +may use :class:`~django.contrib.postgres.fields.RangeOperators` which maps the +operators with strings. For example:: + + expressions=[ + ('timespan', RangeOperators.ADJACENT_TO), + (F('room'), RangeOperators.EQUAL), + ] + +.. admonition:: Restrictions on operators. + + Only commutative operators can be used in exclusion constraints. + +``index_type`` +-------------- + +.. attribute:: ExclusionConstraint.index_type + +The index type of the constraint. Accepted values are ``GIST`` or ``SPGIST``. +Matching is case insensitive. If not provided, the default index type is +``GIST``. + +``condition`` +------------- + +.. attribute:: ExclusionConstraint.condition + +A :class:`~django.db.models.Q` object that specifies the condition to restrict +a constraint to a subset of rows. For example, +``condition=Q(cancelled=False)``. + +These conditions have the same database restrictions as +:attr:`django.db.models.Index.condition`. + +Examples +-------- + +The following example restricts overlapping reservations in the same room, not +taking canceled reservations into account:: + + from django.contrib.postgres.constraints import ExclusionConstraint + from django.contrib.postgres.fields import DateTimeRangeField, RangeOperators + from django.db import models + from django.db.models import Q + + class Room(models.Model): + number = models.IntegerField() + + + class Reservation(models.Model): + room = models.ForeignKey('Room', on_delete=models.CASCADE) + timespan = DateTimeRangeField() + cancelled = models.BooleanField(default=False) + + class Meta: + constraints = [ + ExclusionConstraint( + name='exclude_overlapping_reservations', + expressions=[ + ('timespan', RangeOperators.OVERLAPS), + ('room', RangeOperators.EQUAL), + ], + condition=Q(cancelled=False), + ), + ] + +In case your model defines a range using two fields, instead of the native +PostgreSQL range types, you should write an expression that uses the equivalent +function (e.g. ``TsTzRange()``), and use the delimiters for the field. Most +often, the delimiters will be ``'[)'``, meaning that the lower bound is +inclusive and the upper bound is exclusive. You may use the +:class:`~django.contrib.postgres.fields.RangeBoundary` that provides an +expression mapping for the `range boundaries <https://www.postgresql.org/docs/ +current/rangetypes.html#RANGETYPES-INCLUSIVITY>`_. For example:: + + from django.contrib.postgres.constraints import ExclusionConstraint + from django.contrib.postgres.fields import ( + DateTimeRangeField, + RangeBoundary, + RangeOperators, + ) + from django.db import models + from django.db.models import Func, Q + + + class TsTzRange(Func): + function = 'TSTZRANGE' + output_field = DateTimeRangeField() + + + class Reservation(models.Model): + room = models.ForeignKey('Room', on_delete=models.CASCADE) + start = models.DateTimeField() + end = models.DateTimeField() + cancelled = models.BooleanField(default=False) + + class Meta: + constraints = [ + ExclusionConstraint( + name='exclude_overlapping_reservations', + expressions=( + (TsTzRange('start', 'end', RangeBoundary()), RangeOperators.OVERLAPS), + ('room', RangeOperators.EQUAL), + ), + condition=Q(cancelled=False), + ), + ] diff --git a/docs/ref/contrib/postgres/fields.txt b/docs/ref/contrib/postgres/fields.txt index 387eb0f02e..14cb1d00cb 100644 --- a/docs/ref/contrib/postgres/fields.txt +++ b/docs/ref/contrib/postgres/fields.txt @@ -944,3 +944,26 @@ corresponding lookups. NOT_LT = '&>' NOT_GT = '&<' ADJACENT_TO = '-|-' + +RangeBoundary() expressions +--------------------------- + +.. versionadded:: 3.0 + +.. class:: RangeBoundary(inclusive_lower=True, inclusive_upper=False) + + .. attribute:: inclusive_lower + + If ``True`` (default), the lower bound is inclusive ``'['``, otherwise + it's exclusive ``'('``. + + .. attribute:: inclusive_upper + + If ``False`` (default), the upper bound is exclusive ``')'``, otherwise + it's inclusive ``']'``. + +A ``RangeBoundary()`` expression represents the range boundaries. It can be +used with a custom range functions that expected boundaries, for example to +define :class:`~django.contrib.postgres.constraints.ExclusionConstraint`. See +`the PostgreSQL documentation for the full details <https://www.postgresql.org/ +docs/current/rangetypes.html#RANGETYPES-INCLUSIVITY>`_. diff --git a/docs/ref/contrib/postgres/index.txt b/docs/ref/contrib/postgres/index.txt index 9485f56409..03ff6da1e0 100644 --- a/docs/ref/contrib/postgres/index.txt +++ b/docs/ref/contrib/postgres/index.txt @@ -29,6 +29,7 @@ a number of PostgreSQL specific data types. :maxdepth: 2 aggregates + constraints fields forms functions |