How to prevent overlapping of int ranges

Question

I have a table as follow :

CREATE TABLE appointments (
    id SERIAL PRIMARY KEY,
    date TIMESTAMP NOT NULL,
    start_mn INT NOT NULL,
    end_mn INT NOT NULL,
    EXCLUDE using gist((array[start_mn, end_mn]) WITH &&)
)

I want to prevent start_mn and end_mn overlapping between rows so I've added a gist exclusion :

EXCLUDE using gist((array[start_mn, end_mn]) WITH &&)

But inserting the two following do not trigger the exclusion:

INSERT INTO appointments(date, start_mn, end_mn) VALUES('2020-08-08', 100, 200);
INSERT INTO appointments(date, start_mn, end_mn) VALUES('2020-08-08', 90, 105);

How can I achieve this exclusion ?

score 3 · Accepted Answer · answered Aug 14 '20 at 21:29

If you want to prevent an overlapping range you will have to use a range type not an array.

I also assume that start and end should never overlap on the same day, so you need to include the date column in the exclusion constraint:

CREATE TABLE appointments 
(
    id SERIAL PRIMARY KEY,
    date TIMESTAMP NOT NULL,
    start_mn INT NOT NULL,
    end_mn INT NOT NULL,
    EXCLUDE using gist( int4range(start_mn, end_mn, '[]') WITH &&, "date" with =)
)

If start_mn and end_mn are supposed to be "time of the day", then those columns should be defined as time, not as integers.

How to prevent overlapping of int ranges

1 Answers1