And, the values are actually all there: nettest=> select * from cidr; net ------------- 10.1.0.0/22 10.1.4.0/22 10.1.0.0/20 (3 rows)
While it seems intelligent enough to know that 10.1.1.0/22 would overlap, it doesn't know that 10.1.0.0/20 would envelop other networks that already exist in it's dataset. Am I doing something incorrectly here?
And what if 10.1.0.0/20 covers the range of other networks? Even if Big Org network totally covers the Dept A and Dept B networks, why shouldn't you be able to insert all 3? The Unique constraint should prevent people from successfully inserting Big Org Network more than once, but that's about it.
You're assuming a much narrower range of usage/application for cidr stuff.
While it's possible it's just my viewpoint, it seems that this is kind of weird
behavior. If the datatype has any notion of networks and that data being
'unique' shouldn't it also know about netmasks? I guess it's a different kind
of unique than people are used to seeing. But, you can't have two networks,
one 10.1.0.0/22 and 10.1.0.0/20 on the same wire, or connected to a router, or whatever.
You can have two networks 10.1.0.0/22 and 10.1.0.0/20 on the same router and on the same wire too.
A router could have two different routes: packets to 10.1.0.0/22 will be sent via 10.1.1.1 and the rest matching 10.1.0.0/20 via 10.1.1.2.
The more specific route takes precedence. But that does not exclude the existence of a more general network.
You might be able to create a functional index that does what you want. I don't know how but I think it could be possible.
Regards, Link.
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
