Today I was working with a junior developer, and was lucky enough to be able to explain exclusion constraints to them. I got partway through it before I realised that the Django model we were working on did not have a range field, but instead had a start and a finish.
class Leave(models.Model): person = models.ForeignKey( 'person.Person', related_name='approved_leave', on_delete=models.CASCADE, ) start = models.DateTimeField() finish = models.DateTimeField()
It turns out that this is not a problem. You can use any expression in a constraint:
ALTER TABLE leave_leave ADD CONSTRAINT prevent_overlapping_leave EXCLUDE USING gist(person_id WITH =, TSTZRANGE(start, finish) WITH &&)
Whilst we have application-level validation in place to prevent this, there is a code path that allows it (hence the desire to implement this). Because this is an exclusion constraint, we won’t be able to use the
NOT VALID syntax, but will instead have to either fix the invalid data, or use a
WHERE clause to only apply the constraint to “new” data.
ALTER TABLE leave_leave ADD CONSTRAINT prevent_overlapping_leave EXCLUDE USING gist(person_id WITH =, TSTZRANGE(start, finish) WITH &&) WHERE start > '2019-07-19';
The other benefit of this is that it creates an index that includes
TSTZRANGE(start, finish), which could be used for querying, but also will ensure that
start <= finish for all rows.