1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
|
========================================
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),
),
]
|
