docs/ref/contrib/postgres/constraints.txt
========================================
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``
=======================
.. class:: ExclusionConstraint(*, name, expressions, index_type=None, condition=None, deferrable=None, include=None, violation_error_code=None, violation_error_message=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.
Exclusion constraints are checked during the :ref:`model validation
<validating-objects>`.
``name``
--------
.. attribute:: ExclusionConstraint.name
See :attr:`.BaseConstraint.name`.
``expressions``
---------------
.. attribute:: ExclusionConstraint.expressions
An iterable of 2-tuples. The first element is an expression or string. The
second element is an 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.
The :class:`OpClass() <django.contrib.postgres.indexes.OpClass>` expression can
be used to specify a custom `operator class`_ for the constraint expressions.
For example::
expressions = [
(OpClass("circle", name="circle_ops"), RangeOperators.OVERLAPS),
]
creates an exclusion constraint on ``circle`` using ``circle_ops``.
.. _operator class: https://www.postgresql.org/docs/current/indexes-opclass.html
``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`.
``deferrable``
--------------
.. attribute:: ExclusionConstraint.deferrable
Set this parameter to create a deferrable exclusion constraint. Accepted values
are ``Deferrable.DEFERRED`` or ``Deferrable.IMMEDIATE``. For example::
from django.contrib.postgres.constraints import ExclusionConstraint
from django.contrib.postgres.fields import RangeOperators
from django.db.models import Deferrable
ExclusionConstraint(
name="exclude_overlapping_deferred",
expressions=[
("timespan", RangeOperators.OVERLAPS),
],
deferrable=Deferrable.DEFERRED,
)
By default constraints are not deferred. A deferred constraint will not be
enforced until the end of the transaction. An immediate constraint will be
enforced immediately after every command.
.. warning::
Deferred exclusion constraints may lead to a `performance penalty
<https://www.postgresql.org/docs/current/sql-createtable.html#id-1.9.3.85.9.4>`_.
``include``
-----------
.. attribute:: ExclusionConstraint.include
A list or tuple of the names of the fields to be included in the covering
exclusion constraint as non-key columns. This allows index-only scans to be
used for queries that select only included fields
(:attr:`~ExclusionConstraint.include`) and filter only by indexed fields
(:attr:`~ExclusionConstraint.expressions`).
``include`` is supported for GiST indexes. PostgreSQL 14+ also supports
``include`` for SP-GiST indexes.
``violation_error_code``
------------------------
.. versionadded:: 5.0
.. attribute:: ExclusionConstraint.violation_error_code
The error code used when ``ValidationError`` is raised during
:ref:`model validation <validating-objects>`. Defaults to ``None``.
``violation_error_message``
---------------------------
The error message used when ``ValidationError`` is raised during
:ref:`model validation <validating-objects>`. Defaults to
:attr:`.BaseConstraint.violation_error_message`.
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),
),
]