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"

Debug questions require a [mre]. [ask] [Help] [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) [Why are images of text, code and mathematical expressions discouraged?](https://meta.stackexchange.com/q/320052/266284) — philipxy, Oct 15 '22 at 07:57

Erwin Brandstetter · Answer 1 · 2022-10-15T05:28:40.950

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:

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.

ERROR: could not create exclusion constraint

1 Answers1