Re: OVERLAPS constraint using TIME columns

Gio - wrote:
> 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
ofthe 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.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com