Re: Exclude constraint on ranges : commutative containment : allowonly complete containment

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 &&)
);



> 
> 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
>>>
>>
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com