Mastering PostgreSQL 12 - Third Edition

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