Lets say we have several rooms, and every room has reservations. We do not want the reservations to overlap, ever. Here’s how we add that check to a table in PostgreSQL.
The following will make sure no
end_date overlaps, but completely ignores the check on the room for now.
To get the room into the mix we need the
Now we can include the room, notice the new
room WITH = inside the
If you’re wondering what the
 is about. It specifies the bounds.
(]. In our case
 means we include both the lower and upper bound (start and end date) in the check.
If we would use
[) instead then the
start_date of another entry could be equal to the
end_date of the other one.
This is a simplified take on it, check https://en.wikipedia.org/wiki/Interval_(mathematics) for a detailed look.
You could also use a Range-Type to simplify the
EXCLUDE a bit, see https://www.postgresql.org/docs/current/static/rangetypes.html for more
In an Elixir migration do this