Aggregating inet subnets to supernets

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

Browse pgsql-general by date

  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