September 3rd, 2014 by depesz | Tags: aggregate, cidr, inet, max, min, pg95, postgresql | No comments »

Did it help? If yes - maybe you can help me?

Did it help? If yes - maybe you can help me?

On 29th of August, Tom Lane committed patch:

Add min and max aggregates for inet/cidr data types. Haribabu Kommi, reviewed by Muhammad Asif Naeem |

So, the change is pretty trivial to explain – we now have min() and max() aggregates (as in: functions that you call in “GROUP BY" queries) that work on inet/cidr datatypes. Previously, somehow, they weren't there.

With this, I can, for example do something like:

$ select network(set_masklen(client_ip, 24)), count(*), count(distinct client_ip), min(client_ip), max(client_ip) from test group by 1 order by 2 desc limit 10; network | count | count | min | max -----------------+-------+-------+----------------+---------------- a.b.c.0/24 | 11211 | 39 | a.b.c.66 | a.b.c.237 a.b.c.0/24 | 4180 | 1 | a.b.c.99 | a.b.c.99 a.b.c.0/24 | 4024 | 38 | a.b.c.24 | a.b.c.242 a.b.c.0/24 | 1460 | 2 | a.b.c.104 | a.b.c.110 a.b.c.0/24 | 885 | 1 | a.b.c.240 | a.b.c.240 a.b.c.0/24 | 501 | 1 | a.b.c.112 | a.b.c.112 a.b.c.0/24 | 371 | 1 | a.b.c.126 | a.b.c.126 a.b.c.0/24 | 297 | 1 | a.b.c.200 | a.b.c.200 a.b.c.0/24 | 293 | 1 | a.b.c.171 | a.b.c.171 a.b.c.0/24 | 249 | 6 | a.b.c.20 | a.b.c.187 (10 rows) |

(changed the ips, as these come from logs for explain.depesz.com – so these are real-life logs.

Anyway – this is another feature, that while will not be very broadly used, but will definitely be helpful for people doing stuff using inet addresses.

Thanks guys ðŸ™‚