PostgreSQL specific database constraintsLink to this heading#

nameLink to this heading#

ExclusionConstraint.nameLink to this definition#

See BaseConstraint.name.

expressionsLink to this heading#

ExclusionConstraint.expressionsLink to this definition#

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 RangeOperators which maps the operators with strings. For example:

expressions = [
    ("timespan", RangeOperators.ADJACENT_TO),
    (F("room"), RangeOperators.EQUAL),
]

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

index_typeLink to this heading#

ExclusionConstraint.index_typeLink to this definition#

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.

conditionLink to this heading#

ExclusionConstraint.conditionLink to this definition#

A 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 django.db.models.Index.condition.

deferrableLink to this heading#

ExclusionConstraint.deferrableLink to this definition#

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.

includeLink to this heading#

ExclusionConstraint.includeLink to this definition#

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 (include) and filter only by indexed fields (expressions).

include is supported for GiST indexes. PostgreSQL 14+ also supports include for SP-GiST indexes.

violation_error_codeLink to this heading#

ExclusionConstraint.violation_error_codeLink to this definition#

The error code used when ValidationError is raised during model validation. Defaults to None.

violation_error_messageLink to this heading#

The error message used when ValidationError is raised during model validation. Defaults to BaseConstraint.violation_error_message.

예제Link to this heading#

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 RangeBoundary that provides an expression mapping for the range boundaries. 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),
            ),
        ]