Postgres - range for 'time without time zone' and exclude constraint

Question

I have the following table:

create table booking (
     identifier      integer                not null primary key,
     room            uuid                   not null,
     start_time      time without time zone not null,
     end_time        time without time zone not null
);

I want to create an exclude constraint to enforce that there are no overlapping appointments for the same room.

I tried the following:

alter table booking add constraint overlapping_times
exclude using gist
(
     cast(room as text) with =,
     period(start_time, end_time) with &&)
);

This has two problems:

• Casting room to text is not enough, it gives: ERROR: data type text has no default operator class for access method "gist". I know in v10 there is btree_gist, but I am using v9.5 and v9.6, so I have to manually cast the uuid to a text afaik.

• period(...) is wrong, but I have no idea how to construct a range of time without time zone type.

Answer 1

After installing btree_gist, you can do the following:

create type timerange as range (subtype = time);

alter table booking add constraint overlapping_times
exclude using gist
(
     (room::text) with =,
     timerange(start_time, end_time) with &&
);

If you want an expression in the constraint you need to put that into parentheses. So either (room::text) or (cast(room as text))