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