You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
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.
The text was updated successfully, but these errors were encountered:
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.
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.
The text was updated successfully, but these errors were encountered: