1

Let's say I have a table relaxed_schedule, with a contestant_id and a timeslot of type daterange (or any other range, really)

I know how to use an exclude constraint to prevent dateranges from being directly adjacent, like so:

alter table relaxed_schedule
    add constraint my_exclude_constraint
        exclude using gist ( contestant_id with =, timeslot with -|-);

But what I'm looking for is a way to enforce that the dateranges are adjacent, so quite the opposite. Is there something that comes close to the following?

alter table tight_schedule
    add constraint my_exclude_constraint
        exclude using gist ( contestant_id with =, timeslot with !-|-);

Here's an example fiddle: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=172bf2ef20b1289318872ee171374abf

Paul S
  • 434
  • 4
  • 13
  • Do you have some examples? You could use https://dbfiddle.uk/?rdbms=postgres_14 – Frank Heikens Mar 29 '22 at 14:40
  • Adjacent to what? To the previous one? To all others? What if you delete on from the middle? How do you insert the very first, which is not adjacent to anything? – Bergi Mar 29 '22 at 14:50
  • 1
    No, this is not what an exclusion constraint will do. I'd suggest to write a validation trigger that is deferred until the end of the transaction. – Bergi Mar 29 '22 at 14:51
  • @Bergi Thanks, I think you're right and there is no way to enforce adjacency like this. I must have been pulled into this direction because it worked so well for the other requirement I have for this table: there may be no overlap between any timeslots. (Replace the -|- operator with the && operator for that) – Paul S Mar 29 '22 at 15:28

0 Answers0