Taking advantage of exclusion operators

So far, indexes have been used to speed things up and to ensure uniqueness. However, a couple of years ago, somebody came up with the idea of using indexes for even more. As you have seen in this chapter, GiST supports operations such as intersects, overlaps, contains, and many others. So, why not use those operations to manage data integrity?

Here is an example:

test=# CREATE EXTENSION btree_gist;
test=# CREATE TABLE t_reservation ( 
        room int, 
        from_to tsrange, 
        EXCLUDE USING GiST (room with =, 
                            from_to with &&) 
); 
CREATE TABLE 

The EXCLUDE USING GiST clause defines additional constraints. If you are selling rooms, you might want to allow different rooms to be booked at the same time. However, you don't want to sell the same room twice during the same period. What the EXCLUDE clause says in my example is this: if a room is booked twice at the same time, an error should pop up (the data in from_to must not overlap (&&) if it is related to the same room).

The following two rows will not violate the constraints:

test=# INSERT INTO t_reservation 
          VALUES (10, '["2017-01-01", "2017-03-03"]');
INSERT 0 1 
test=# INSERT INTO t_reservation 
          VALUES (13, '["2017-01-01", "2017-03-03"]'); 
INSERT 0 1 

However, the next INSERT will cause a violation because the data overlaps:

test=# INSERT INTO t_reservation 
           VALUES (13, '["2017-02-02", "2017-08-14"]');
psql: ERROR:  conflicting key value violates exclusion constraint "t_reservation_room_from_to_excl" 
DETAIL:  Key (room, from_to)=(13, ["2017-02-02 00:00:00","2017-08-14 00:00:00"]) conflicts with existing key (room, from_to)=(13, ["2017-01-01 00:00:00","2017-03-03 00:00:00"]). 

The use of exclusion operators is very useful and can provide you with highly advanced means to handle integrity.