From: | Gio - <gio-force(dot)2(dot)1(at)hotmail(dot)com> |
---|---|
To: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
Cc: | "pgsql-novice(at)postgresql(dot)org" <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: OVERLAPS constraint using TIME columns |
Date: | 2018-02-22 09:37:03 |
Message-ID: | VI1PR05MB158201E392660FD4C8CCD743FDCD0@VI1PR05MB1582.eurprd05.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Alright, thanks!
On 22 Feb 2018, at 11:30, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at<mailto:laurenz(dot)albe(at)cybertec(dot)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<https://www.cybertec-postgresql.com/>
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Watkins | 2018-02-22 16:28:09 | Differing data directories |
Previous Message | Laurenz Albe | 2018-02-22 09:30:55 | Re: OVERLAPS constraint using TIME columns |