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:03:59 |
Message-ID: | VI1PR05MB158276144523B8654F8150EEFDCD0@VI1PR05MB1582.eurprd05.prod.outlook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(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.
>
> Yours,
> Laurenz Albe
> --
> Cybertec | https://www.cybertec-postgresql.com
From | Date | Subject | |
---|---|---|---|
Next Message | Laurenz Albe | 2018-02-22 09:30:55 | Re: OVERLAPS constraint using TIME columns |
Previous Message | Laurenz Albe | 2018-02-22 08:58:44 | Re: OVERLAPS constraint using TIME columns |