ERROR: could not create exclusion constraint

Question

I have a table:

CREATE TABLE attendances
(
    id_attendance serial PRIMARY KEY,
    id_user integer NOT NULL 
         REFERENCES users (user_id) ON UPDATE CASCADE ON DELETE CASCADE,
    entry_date timestamp with time zone DEFAULT NULL,
    departure_date timestamp with time zone DEFAULT NULL,
    created_at timestamp with time zone DEFAULT current_timestamp
);

I want to add an exclusion constraint avoiding attendance to overlap (There can be multiple rows for the same day, but time ranges cannot overlap).

So I wrote this code to add the constraint:

ALTER TABLE attendances 
   ADD CONSTRAINT check_attendance_overlaps
   EXCLUDE USING GIST (box(
      point(
        extract(epoch from entry_date at time zone 'UTC'),
        id_user
      ),
      point(
         extract(epoch from departure_date at time zone 'UTC') - 0.5,
         id_user + 0.5
      )
  )
WITH && );

But when I tried to run it on the database I got this error:

Error: could not create exclusion constraint "check_attendance_overlaps"

Answer 1

To exclude overlapping time ranges per user, work with a multicolumn constraint on id_user and a timestamptz range (tstzrange).

You need the additional module btree_gist once per database:

CREATE EXTENSION IF NOT EXISTS btree_gist;

Then:

ALTER TABLE attendances ADD CONSTRAINT check_attendance_overlaps
EXCLUDE USING gist (id_user WITH =
                  , tstzrange(entry_date, departure_date) WITH &&)

See:

• Store the day of the week and time?
• Postgres constraint for unique datetime range

Or maybe spgist instead of gist. Might be faster. See:

• Perform this hours of operation query in PostgreSQL

Of course, there cannot be overlapping rows in the table, or adding the constraint will fail with a similar error message.