# Using Postgres ExclusionConstraint

**URL:** <https://forum.djangoproject.com/t/using-postgres-exclusionconstraint/37664>\
**Category:** Using the ORM\
**Created:** [January 3, 2025, 3:19pm UTC](https://forum.djangoproject.com/t/using-postgres-exclusionconstraint/37664 "2025-01-03T15:19:08Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![HBarotov](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/hbarotov/32/26901_2.png) [@HBarotov](https://forum.djangoproject.com/u/HBarotov)\
**Post date:** [January 3, 2025, 3:19pm UTC](https://forum.djangoproject.com/t/using-postgres-exclusionconstraint/37664/1 "2025-01-03T15:19:08Z")

</div>

Versions:  
django==5.1.4  
python==3.12.8  
postgres v15.4

How to ensure that there is no overlap in the class schedules?  
I am modeling an educational system CRM. All the models are working fine, and I am aiming to add constraints for my GroupSchedule model

```auto
class GroupSchedule(models.Model):
    DAYS = [
        (1, _("Monday")),
        (2, _("Tuesday")),
        (3, _("Wednesday")),
        (4, _("Thursday")),
        (5, _("Friday")),
        (6, _("Saturday")),
        (7, _("Sunday")),
    ]
    id = ShortUUIDField(length=16, max_length=40, primary_key=True, editable=False)
    group = models.ForeignKey(Group, related_name="schedules", on_delete=models.CASCADE)
    teacher = models.ForeignKey(
        Employee,
        related_name="schedules",
        on_delete=models.PROTECT,
        limit_choices_to=Q(role="TE") | Q(role="OW"),
    )
    room = models.ForeignKey(
        Room, related_name="schedules", on_delete=models.SET_NULL, null=True
    )
    day = models.PositiveSmallIntegerField(choices=DAYS)
    start_time = models.TimeField()
    end_time = models.TimeField()

    start_minutes = models.PositiveSmallIntegerField(editable=False)
    end_minutes = models.PositiveSmallIntegerField(editable=False)

    class Meta:
        ordering = ["day", "start_time"]
        constraints = [
            CheckConstraint(
                check=Q(start_time__lt=F("end_time")),
                name="start_time_before_end_time",
                violation_error_message="End time must be greater than start time",
            ),
        ]

    def __str__ (self):
        return (
            f"{self.group}, {self.get_day_display()} {self.start_time}:{self.end_time}"
        )

    def save(self, *args, **kwargs):
        self.start_minutes = self.start_time.hour * 60 + self.start_time.minute
        self.end_minutes = self.end_time.hour * 60 + self.end_time.minute
        super().save(*args, **kwargs)

```

I want to create a database-level constraint that rejects overlapping schedules for teacher and/or group. A group or teacher cannot be present in two schedules at the same time. With the help of documentation and GPT, I installed the `BtreeGistExtension()` in my migrations and added this constraint for group:

```auto
ExclusionConstraint(
    name="exclude_group_overlap",
    expressions=[
        (("group_id", "gist_btree_ops"), "="),
        (("day", "gist_int2_ops"), "="),
        (
            (
                Range("start_minutes", "end_minutes", bounds="[]"),
                "gist_int4range_ops",
            ),
            "&&",
        ),
    ],
)

```

However, no matter what I do, it is not working and always returning the same message.

```auto
django.db.utils.ProgrammingError: data type unknown has no default operator class for access method "gist"
HINT: You must specify an operator class for the index or define a default operator class for the data type.

```

It is the first time I am going this deep into ORM. I am now lost: is this the way to ensure no overlaps or am I fighting the Django? Am I better off (trying to) write raw SQL or just do a form-level validation (I am going with a monolithic approach with Django templates and HTMX)?

Thanks for your time.

---

<div class="post-metadata">

**Author:** ![anefta](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/anefta/32/10649_2.png) [@anefta](https://forum.djangoproject.com/u/anefta)\
**Post date:** [January 4, 2025, 3:14pm UTC](https://forum.djangoproject.com/t/using-postgres-exclusionconstraint/37664/2 "2025-01-04T15:14:23Z")

</div>

You could try to validate the constraint logic on the clean () method:

```auto
from django.core.exceptions import ValidationError
from django.db.models import Q

class GroupSchedule(models.Model):
    # ... your existing fields ...

    def clean(self):
        super().clean()
        
        # Check for teacher schedule conflicts
        teacher_conflicts = GroupSchedule.objects.filter(
            teacher=self.teacher,
            day=self.day,
        ).exclude(pk=self.pk)

        teacher_conflicts = teacher_conflicts.filter(
            Q(start_minutes__lt=self.end_minutes) &
            Q(end_minutes__gt=self.start_minutes)
        )

        if teacher_conflicts.exists():
            raise ValidationError({
                'start_time': 'This schedule conflicts with another schedule for this teacher.'
            })

        # Check for group schedule conflicts
        group_conflicts = GroupSchedule.objects.filter(
            group=self.group,
            day=self.day,
        ).exclude(pk=self.pk)

        group_conflicts = group_conflicts.filter(
            Q(start_minutes__lt=self.end_minutes) &
            Q(end_minutes__gt=self.start_minutes)
        )

        if group_conflicts.exists():
            raise ValidationError({
                'start_time': 'This schedule conflicts with another schedule for this group.'
            })

```