Re: Exclude constraint on ranges : commutative containment : allowonly complete containment - Mailing list pgsql-general

From Achilleas Mantzios
Subject Re: Exclude constraint on ranges : commutative containment : allowonly complete containment
Date
Msg-id ed8358a5-45d0-258f-bc4a-86b082a98d60@matrix.gatewaynet.com
Whole thread Raw
In response to Re: Exclude constraint on ranges : commutative containment : allowonly complete containment  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Exclude constraint on ranges : commutative containment : allowonly complete containment
List pgsql-general
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 (basic budget
pointingto 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.

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 trigger
whichis always an option).|
 
>>
>> -- 
>> Achilleas Mantzios
>> IT DEV Lead
>> IT DEPT
>> Dynacom Tankers Mgmt
>>
>
>


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




pgsql-general by date:

Previous
From: Achilleas Mantzios
Date:
Subject: Re: Exclude constraint on ranges : commutative containment : allowonly complete containment
Next
From: Durumdara
Date:
Subject: Add column with default value in big table - splitting of updates can help?