Mastering PostgreSQL 9.6

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 ...