How to make an EXCLUDE constraint with date + time values? (PostgreSQL)

Question

As the title suggests, what I'm trying is to create an EXCLUDE constraint by concatenating date + time values.

Here is a DB table called bookings:

column name | data type
----------------------
date        | date
start_time  | time
end_time    | time

start_time and end_time aren't timestamp. They are time.

Here's a SQL query:

ALTER TABLE bookings
ADD CONSTRAINT overlapping_times
EXCLUDE USING GIST (
  tsrange(date + start_time, date + end_time) WITH &&
)

The purpose for the constraint is to prevent bookings table from having multiple rows with overlapping time ranges on the same date.

tsrange is available for timestamps, not time. So, by following Postgres' doc, I concatenate date and start_time (and end_time). The doc shows the following example:

date + time → timestamp
Add a time-of-day to a date
date '2001-09-28' + time '03:00' → 2001-09-28 03:00:00

However, the SQL above results in:

error: data type integer has no default operator class for access method "gist"

What am I doing wrong?

EDIT

Thank you @Tim Otomagis. It seems that I was using an old version of Postgres which can't create timestamp by date + time

So, by following this, I tried the following:

CREATE EXTENSION btree_gist;

ALTER TABLE bookings
ADD CONSTRAINT overlapping_times
EXCLUDE USING GIST (
    tsrange(
        CONCAT(date::date || ' ' || start_time::time)::timestamp,
        CONCAT(date::date || ' ' || end_time::time)::timestamp
    ) WITH &&
);

However, this results in:

functions in index expression must be marked IMMUTABLE

EDIT 2

I ended up upgrading Postgres from v12 to v14 to make the original approach work. Thanks again @Tim Otomagis

Answer 1

sorry I was unable to recreate the problem. when I try SQL code above it works as expected. I'm using PostgreSQL 14.

sample result with DBeaver