Re: OVERLAPS constraint using TIME columns

On 22 Feb 2018, at 11:30, 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 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 &&
                        );

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