Re: OVERLAPS constraint using TIME columns

Hi Laurenz, thank you for your reply.

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.

Best regards

> On 22 Feb 2018, at 10:58, Laurenz Albe <laurenz.albe@cybertec.at> wrote:
> 
> 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