Postgresql Rocks

Postgresql supports a data type "inet". Inet allows you to insert ip addresses into the table, and then later run operations against the host/network ranges. A while back I accidentally created a schema using the inet data type, today I was able to search for transactions based on a subnet mask (yes it's very cool).

select tochar(date, 'YYYY-MM-DD') as date, round(sum(size)/2/1073741824,3) as size, account, appuid, clientip from proftpd where method = 'RETR' and tochar(date, 'YYYY-MM') = '2004-03' and clientip << inet '10.1.0.160/28' group by to_char(date, 'YYYY-MM-DD'), account, appuid, clientip;

That "clientip << inet '10.1.0.160/28'" section is key. It says where clientip is in the 10.1.0.160/28 subnet or has a 10.1.0.160-10.1.0.175 ip address.