Re: Exclude constraint on ranges : commutative containment : allow only complete containment

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.

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