Re: Exclude constraint on ranges : commutative containment : allowonly complete containment - Mailing list 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
completecontainment, 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
coveringa 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
numberof 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
(basicbudget 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
overlapwith 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
eithercontained 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
triggerwhich is always an option).|
 
>>>>
>>>> -- 
>>>> Achilleas Mantzios
>>>> IT DEV Lead
>>>> IT DEPT
>>>> Dynacom Tankers Mgmt
>>>>
>>>
>>>
>>
>>
>
>


-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt