Обсуждение: OVERLAPS constraint using TIME columns

Hi all,

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
theday). How can I add
 
such a constraint with these columns? 

Thank you very much,
George

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

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

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
startof 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 &&
                         );

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