How would I aggregate a lot of inet addresses/subnets to unique super-
networks? Simply doing a 'GROUP BY network(address)' will not do any
aggregation, and thus includes lots of /32s that are part of larger
networks. While I could add 'WHERE masklen(address) <> 32 and family
(address) = 4' (or ditto for IPv6), I'd rather avoid it. The addresses
are gathered from my servers/routers/etc and always include the cidr
mask length on INSERT into the db.
Bonus question: How would I construct a query for aggregating at an
arbitrary depth? E.g. if there are three levels of subnetting:
10.0.0.0/16 is split in a bunch of 10.0.0.0/22s, and some of those
are split in bunches of /24s; a 1st level aggregation would output
the /22s *and* the /16, and a 2nd level aggregation would then
output only the /16.
Input:
address (inet) | comment
-------------------+------------------------------------------------------
10.0.0.12/25 | Host addr in 10.0.0.0/25 net
10.0.0.22/25 | Host addr in 10.0.0.0/25 net
10.0.0.54 | Virtual /32 IP in 10.0.0.0/25 net
10.0.0.55 | Virtual /32 IP in 10.0.0.0/25 net
10.0.0.56 | Virtual /32 IP in 10.0.0.0/25 net
10.0.0.97/27 | Host addr in 10.0.0.96/27 subnet, in 10.0.0.0/25 net
10.0.1.12/24 | Host addr in 10.0.1.0/24 net
2001:1:2:3::12/64 | Host addr in 2001:1:2:3::/64 net
2001:1:2:3::13 | Virtual /128 IP in 2001:1:2:3::/64 net
Desired output:
network
-----------------
10.0.0.0/25
10.0.1.0/24
2001:1:2:3::/64
regards,
Sven
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
