Using Postgres ExclusionConstraint

Using Django Using the ORM
1

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

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:

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.

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.

2

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

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.'
            })
1 Like