From: | Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> |
---|---|
To: | pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: Exclude constraint on ranges : commutative containment : allow only complete containment |
Date: | 2020-01-30 17:03:42 |
Message-ID: | 85227bd5-99b7-a820-f251-a8833490ac04@matrix.gatewaynet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 30/1/20 6:49 μ.μ., Adrian Klaver wrote:
> On 1/30/20 3:46 AM, Achilleas Mantzios wrote:
>> On 29/1/20 8:32 μ.μ., Adrian Klaver wrote:
>>> On 1/29/20 8:12 AM, Achilleas Mantzios wrote:
>>>> Hello Dear Postgresql ppl,
>>>> I have a table with date ranges and need to express the following constraint : allow overlaps only if there is complete containment, e.g.
>>>> allow values in rows like :
>>>> [2020-01-01,2020-01-31)
>>>> [2020-01-02,2020-01-10)
>>>> [2020-01-10,2020-01-20)
>>>>
>>>> but disallow rows like
>>>>
>>>> [2020-01-02,2020-01-10)
>>>> [2020-01-08,2020-01-11)
>>>
>>> I'm missing something. Can you provide a more complete example?
>> Dear Adrian,
>> I can give an example, lets say that we want to model the concept of budget, and we allow basic complete budgets covering a specific time period (daterange) which will have predictions and also
>> matched actual transactions (Debits, Credits) , but also want "super" budgets of larger time periods which include a number of basic budgets (sub-budgets) and for which only predictions are
>> allowed, not actual transactions. We could make the design strict and explicit by using referential constraints (basic budget pointing to a super budget) but If we chose to not make it strict , and
>> conversely allow a more dynamic and liberal way that the system detects one form or the other , we could say :
>> each sub-budget (of the lowest level of the hierarchy - although for the time being we have only two levels) cannot overlap with any other sub-budget
>> each super-budget can only fully contain its sub-budgets , no partial overlap allowed.
>
> Um, that makes my head hurt:) Questions:
>
> 1) Are the basic complete budgets and the sub-budgets and super budgets existing in the same table?
>
> 2) Depending on answer to 1, to prevent overlap could you not use a form of the example here?:
>
> https://www.postgresql.org/docs/12/rangetypes.html#RANGETYPES-CONSTRAINT
>
> CREATE TABLE reservation (
> during tsrange,
> EXCLUDE USING GIST (during WITH &&)
> );
>
same table.
overlap is allowed but only when it is complete containment, which is the whole point of this thread.
>
>
>>
>> This could be solved easily if there was a commutative containment operator like :
>> CREATE OR REPLACE FUNCTION range_containment(anyrange, anyrange)
>> RETURNS boolean
>> LANGUAGE sql
>> IMMUTABLE PARALLEL SAFE STRICT
>> AS $function$
>> select $1 <@ $2 OR $1 @> $2;
>> $function$;
>>
>> so range_containment returns true if the first operand is contained in the second or contains the second.
>>
>> create operator <@@> (PROCEDURE=range_containment, LEFTARG=anyrange, RIGHTARG=anyrange, COMMUTATOR = <@@> );
>>
>> But unfortunately :
>>
>> alter table bdynacom.acc_budget ADD CONSTRAINT acc_budget_start_end EXCLUDE USING gist (daterange(period_start, period_end, '[]'::text) WITH <@@>);
>> ERROR: operator <@@>(anyrange,anyrange) is not a member of operator family "range_ops"
>> DETAIL: The exclusion operator must be related to the index operator class for the constraint.
>>
>> From a small research I did this might mean recompiling the source to make <@@> member of range_ops .
>>
>>>
>>>>
>>>> I think that writing a new commutative range operator e.g. |<@@> which would return true if the left operand is either contained by or contains the right operand and false otherwise would solve
>>>> this, I am just wondering if there is a more elegant and economical way to express this. (besides writing a trigger which is always an option).|
>>>>
>>>> --
>>>> Achilleas Mantzios
>>>> IT DEV Lead
>>>> IT DEPT
>>>> Dynacom Tankers Mgmt
>>>>
>>>
>>>
>>
>>
>
>
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt
From | Date | Subject | |
---|---|---|---|
Next Message | PegoraroF10 | 2020-01-30 21:59:15 | Options for Postgres FDW |
Previous Message | Adrian Klaver | 2020-01-30 16:49:54 | Re: Exclude constraint on ranges : commutative containment : allow only complete containment |