Taking advantage of exclusion operators

So far, indexes have been used to speed up things and to ensure uniqueness. However, a couple of years ago somebody come 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 a lot more. 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 the room is equal, the data in from_to with must not overlap (&&).

The following two rows will not violate 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"]'); 
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.