I have a table in a PostgreSQL database in which I store bookings that span periods of time. There must be at least 45 minutes between such bookings. I currently have this constraint on the table:
EXCLUDE USING GIST (
tsrange(start_time AT TIME ZONE 'UTC', end_time END AT TIME ZONE 'UTC' + interval '45 minutes', '[)') WITH &&
)
This prevents addition of a new row that has a start time which is less than 45 minutes after some existing row's end time or an end time which is less than 45 minutes before an existing start time. Now, the bookings can be in different states such as "requested" and "accepted". The 45-minute rule applies only for accepted bookings (it should be possible for bookings to overlap as long as they are all "requested"). I tried to solve this by adding WHERE status='accepted' to the constraint, but that won't catch adding a new booking (which defaults to "requested") that overlaps an accepted one. I need a WHERE clause that will cause the constraint to be evaluated every time an existing row has status='accepted' even though the new row is 'requested'. I guess I want the WHERE clause to consider the existing rows only, not the new one.
I also need to automatically resolve the conflicts that arise when I have several overlapping bookings and change one of them to "accepted". I have a third state, "rejected", that I wish to update all of the rows that now cause conflicts to.
Is it at all possible to do this using an exclusion constraint? Do I need to find some other way of handling the problem? I would like to implement it in the database, because if I don't I'll run in to all sorts of headaches with concurrency and whatnot.
