I have a table for weekly time slots with columns day, from, to.
I would like to add a constraint so that overlapping time slots cannot be added to the db.
The OVERLAPS operator works as I need to, eg SELECT (TIME ‘5:00', TIME '10:00') OVERLAPS (TIME '22:59', TIME '23:10');
But I can’t use it inside a constraint (ERROR: syntax error near “,”)
ALTER TABLE slots ADD CONSTRAINT same_day_slots_overlap EXCLUDE USING GIST ( day WITH =, (from, to) WITH OVERLAPS );
Same error happens if I use the && operator.
I only need time information in my columns so I can either model them as TIME or INTEGER (as minutes from the start of the day). How can I add such a constraint with these columns?
What about
ALTER TABLE slots ADD CONSTRAINT same_day_slots_overlap EXCLUDE USING gist (tsrange(day + "from", day + "to") WITH &&);
It is strange to store date and time separately. That way you cannot use "timestamp with time zone", which is almost always the correct data type to use.
I forgot to mention that the ‘day’ column only contains the name of a day eg Monday, Tuesday, Wednesday etc. It does not contain any info about which day of the month it is.
What I want to model is weekly - recurring time slots.
If you install the "btree_gist" extension, you can create:
ALTER TABLE slots ADD CONSTRAINT same_day_slots_overlap EXCLUDE USING gist (day WITH =, int4range( CAST(EXTRACT(hour FROM "from") + EXTRACT(minute FROM "from") * 60 AS integer), CAST(EXTRACT(hour FROM "to") + EXTRACT(minute FROM "to") * 60 AS integer) ) WITH && );
