We're gonna look at one more type of constraint in this module. We're gonna save foreign key constraints for indexing, it makes a little more sense over there. The exclusion constraint is very similar to a unique constraint, but with a little bit more power and a little bit more flexibility. You won't use them as often as a unique constraint, but when you need them, they are incredibly cool.

Let me show you. We have here a table for reservations, just a regular ID and then a room ID. And then the reservation period is a TS range. Where we're going to start is by saying that there can be no overlapping ranges in this table. That's not a great place to end, but it is a great place to start. Let's say we want to exclude using GIST, and GIST is a type of index that we will talk about later. And in here you put your criteria. How are we determining when we should exclude a row? We're saying reservation period, and then you pass through, you say width, and then you pass the operator in.

In this case we're gonna say, let's use the overlap operator. Functionally what we've done here is we've said when we are inserting a new row or updating a row, we need to check all the reservation periods and compare it with this double and operator to see if any of the periods overlap the one that we're trying to insert. If they do overlap, we need to exclude it. We need to say, nope, I'm sorry, you cannot insert that row. Let's create this table and then I'm just gonna paste this in here. Insert into reservations, room ID and reservation period. This reservation period is saying 0901 to 0903 and checkout is at noon and you have to be out by noon because the upper bound is not included. If we insert that, that looks good.

Let's just change this to 0204. The original reservation is 0103 and we're saying 02 to 04. That is an overlap. If we try to run that, we see, nope, you cannot do that. It conflicts with the existing key. That's pretty good. However, this is very, very wrong because if somebody tries to book a different room, it says, "Nope, you can't do that." That already overlaps with the existing key. We need to add another criteria to our exclusion constraint. That is we need to compare the room ID along with the reservation period because reservation periods can totally overlap, provided they're not overlapping in the same room.

Let's see if we can update this just a little bit. Let's drop table reservations. Let's get that outta there. What we need to do now is we need to say, "Room ID with strict equality," so room ID with strict equality, and then check the overlap of the reservation period. However, if we run that, you see we have a problem. Data type integer, which room ID is integer, has no default operator class for access method GIST. We're getting a little bit into the weeds here, but what this is saying is that this type of index has no operator for strict equality. We can get around that by enabling something called btree gist. We're gonna say create extension if not exists, btree gist. Then when we create that extension, now we can create that table. This adds a little bit of functionality to (indistinct) GIST index that allows strict equality, which is the btree. That's what a b tree is very good at. This is kind of combining those two things.

We will talk about knowing what extensions you have ready for you, knowing what extensions you have enabled, some common extensions. We will talk about all of that. But for now, we're just going to create the btree gist. With this table now created, let's get that out of the way and we're gonna try this again. Let's go back to room ID number one. We're gonna say September 2nd to September 4th. We can insert that. Room ID number two. Can they book the same period? They sure can, but for room ID number two, let's say somebody tries to book September 3rd to September 5th, and now if we run that, you'll see our key is two columns wide. It says it does conflict with the existing key, which in our case is an existing reservation. Now we have strictly enforced that for a given room, there cannot be a reservation overlap and it just never will enter the database. That gives me great comfort.

Let's look at one more example. We're working with the exact same table here, but I want to make it a little bit more realistic. Let's say that we have a booking status in here. We could make it an enum, but who has the time? I'm just gonna make it a text. Here's what I want to see if we can prove. We should be able, let's create the table. We should be able to insert a canceled reservation and then using the exact same range, insert a, let's just say confirmed reservation. We can't, right? Because we have this exclusion constraint up here that simply says, compare the room ID and the reservation period. If those overlap, then exclude it. That's not exactly what we were really looking for. If we do drop table reservations, we're not going to add anything in here. We're not gonna add anything into the conditions right here.

What we're gonna do is we're going to add it out here. We're basically going to make this a partial exclusion. And the partial part is we want where booking status does not equal canceled. If we create it this way, and we insert a canceled reservation, so we insert a canceled reservation and then using the exact same room ID, exact same timestamps, we can insert anything but canceled and we'll say confirmed. We can't insert confirmed twice because we're only excluding the ones where the booking status equals canceled. We have an exclusion that is much closer to reality when somebody cancels the room. That becomes available for us to book again using this what is called a predicate on our exclusion constraint.