Re: Exclude constraint on ranges : commutative containment : allowonly complete containment - Mailing list pgsql-general
From | Adrian Klaver |
---|---|
Subject | Re: Exclude constraint on ranges : commutative containment : allowonly complete containment |
Date | |
Msg-id | 9c518847-d382-2982-bd55-c9c6e35575da@aklaver.com Whole thread Raw |
In response to | Re: Exclude constraint on ranges : commutative containment : allowonly complete containment (Achilleas Mantzios <achill@matrix.gatewaynet.com>) |
Responses |
Re: Exclude constraint on ranges : commutative containment : allowonly complete containment
|
List | pgsql-general |
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
pgsql-general by date: