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