How to restrict overlapping datetimes in django model at DB level using CheckConstraint

Question

I have the following fields in my Model:

class Event(models.Model):
    starts = models.DateTimeField()
    ends = models.DateTimeField()
    

I want to restrict overlapping dates (starts, ends). I have managed to do this in model validation, but now I want this enforced at database level such that an IntegrityError exception is thrown if an insert happens outside the model's save method.

My Validation was as follows:

...
def clean(self):
    if self.starts and self.ends:
        if self.__class__.objects.filter(
            models.Q(ends__gte=self.starts, starts__lt=self.starts) | models.Q(ends__gte=self.ends, starts__lt=self.ends) | models.Q(starts__gt=self.starts, ends__lt=self.ends)
        ).exists():
            raise ValidationError('Event times overlap with existing record!')

This works. Say an event starting 2020 Oct 11 @ 19:00, and ending 2020 Oct 11 @ 20:00, the following values will prompt an overlap:

1. same date, starting @ 18:00, ending @ 21:00
2. same date, starting @ 19:30, ending @ 19:50
3. same date, starting @ 19:30, ending @ 20:50

But there are situations where the model's .clean() method will not be invoked, which may result in invalid data to be inserted.

My question is, how can I enforce a constraint on the model, which will apply on the database itself, like unique_together does.

I have used postgres specific fields like DateRangeField but in this case, their functionality is limited as they can contain empty upper values to mention one.

I have also come accross this question here on S/O which implements the new (from django 2.2) CheckConstraint, which I have tried to implement, but it doesn't work.

Answer 1

I have used postgres specific fields like DateRangeField but in this case, their functionality is limited as they can contain empty upper values to mention one.

Why not just add an additional constraint to prevent empty upper values? Then you can get all of the benefits of DateRangeField.

Either way, these days we have ExclusionConstraint for postgres, and there are examples in the docs for using ExclusionConstraint with range fields or with two separate fields like your current model.