September 3rd, 2014 by depesz | Tags: , , , , , , | No comments »
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 🙂

Leave a comment