prefix icon indicating copy to clipboard operation
prefix copied to clipboard

No way to determine overlapping prefixes

Open rrb3942 opened this issue 1 year ago • 0 comments

I'm trying to add a constraint to a table to prevent overlapping prefixes with the same length. So a prefix of 1 and 1[2-5] are fine, but if you then also try to add a prefix of 1[3-7] it should fail because it overlaps and has the same length as 1[2-5].

I ran the following to see if I could us any of the supported operators to detect an overlap, but it doesn't appear so.

select a, b,
  a <= b as "<=", a < b as "<", a = b as "=", a <> b as "<>", a >= b as ">=", a > b as ">",
  a @> b as "@>", a <@ b as "<@", a && b as "&&"
from  (select a::prefix_range, b::prefix_range
         from (values('123[2-3]', '123[4-7]'),
                     ('123[2-5]', '123[4-7]')) as t(a, b)
      ) as x;
    a     |    b     | <= | < | = | <> | >= | > | @> | <@ | && 
----------+----------+----+---+---+----+----+---+----+----+----
 123[2-3] | 123[4-7] | t  | t | f | t  | f  | f | f  | f  | t
 123[2-5] | 123[4-7] | t  | t | f | t  | f  | f | f  | f  | t

Even though 123[2-3] doesn't overlap, and 123[2-5] does overlap with 123[4-7], all operators return the same values.

I believe the && operator checks for overlap in both directions, but there does not appear to be a way to check for an overlap only in one direction.

Is there any way to detect this type of overlap? Maybe a missing operator?

I am using version 1.2.10-2 with PostgreSQL version 16.

rrb3942 avatar Oct 29 '24 23:10 rrb3942