========================================

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, opclasses=(), 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>`.

    .. versionchanged:: 4.1

        In older versions, exclusion constraints were not checked during model

        validation.

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

.. versionchanged:: 4.1

    Support for the ``OpClass()`` expression was added.

.. _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 on PostgreSQL 12+ and SP-GiST

indexes on PostgreSQL 14+.

.. versionchanged:: 4.1

    Support for covering exclusion constraints using SP-GiST indexes on

    PostgreSQL 14+ was added.

``opclasses``

-------------

.. attribute:: ExclusionConstraint.opclasses

The names of the `PostgreSQL operator classes

<https://www.postgresql.org/docs/current/indexes-opclass.html>`_ to use for

this constraint. If you require a custom operator class, you must provide one

for each expression in the constraint.

For example::

    ExclusionConstraint(

        name='exclude_overlapping_opclasses',

        expressions=[('circle', RangeOperators.OVERLAPS)],

        opclasses=['circle_ops'],

    )

creates an exclusion constraint on ``circle`` using ``circle_ops``.

.. deprecated:: 4.1

    The ``opclasses`` parameter is deprecated in favor of using

    :class:`OpClass() <django.contrib.postgres.indexes.OpClass>` in

    :attr:`~ExclusionConstraint.expressions`.

``violation_error_message``

---------------------------

.. versionadded:: 4.1

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

                ),

            ]