Preventing insertion of overlapping date ranges using using days of the week and tsrange

I hope I’ll explain it well.

My table has the following structure

  Column   |  Type   | Collation | Nullable | Default | Storage  | Stats target | Description
-----------+---------+-----------+----------+---------+----------+--------------+-------------
 name      | text    |           |          |         | extended |              |
 days_week | text    |           |          |         | extended |              |
 datetime  | tsrange |           |          |         | extended |              |
Indexes:
"test_name_datetime_excl" EXCLUDE USING gist (name WITH =, datetime WITH &&)

I have been trying to insert the same date and time for the same name but only changing the days_week. I know something is missing on the structure of my table but I just don’t know what. This is the sample of what I’m trying to achieve:

Insert into test values (‘first name’,’ Monday’, ‘[2020-01-01 08:30, 2020-01-01 10:00)’);
INSERT 0 1

Insert into test values (‘first name’,’ Monday’, ‘[2020-01-01 08:30, 2020-01-01 10:00)’);  this will be a error or conflicts with existing key

Insert into test values (‘first name’,’ Monday’, ‘[2020-01-01 10:00, 2020-01-01 10:30)’);
INSERT 0 1

Insert into test values (‘first name’,’ Tuesday’, ‘[2020-01-01 08:30, 2020-01-01 10:00)’);
INSERT 0 1

Insert into test values (‘second name’,’ Monday’, ‘[2020-01-01 08:30, 2020-01-01 10:00)’);
INSERT 0 1

Insert into test values (‘second name’,’ Monday’, ‘[2020-01-01 08:30, 2020-01-01 10:00)’);  this will be a error or conflicts with existing key

Insert into test values (‘second name’,’ tuesday’, ‘[2020-01-01 08:30, 2020-01-01 10:00)’);
INSERT 0 1

I hope I explained it well. Thanks in advance

Answer

I think that you just want to add days_week to the excluding index:

create table test (
    name text,
    days_week text,
    datetime tsrange,
    exclude using gist (name with =, days_week with =, datetime with &&)
)

This forbids overlapping ranges on the same name and days_week, while allowing other cases.

Demo on DB Fiddle

Advertisement

Answer