From: | Sven Ulland <sveniu(at)opera(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Aggregating inet subnets to supernets |
Date: | 2013-02-04 20:53:25 |
Message-ID: | 51101FC5.5030003@opera.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Gurjeet Singh | 2013-02-04 21:04:38 | Re: Options for passing values to triggers? |
Previous Message | Moshe Jacobson | 2013-02-04 20:51:39 | Passing dynamic parameters to a table-returning function |