03 October 2005

Network address hacking in your database

Arjen’s post on MySQL’s INET_ATON and INET_NTOA functions reminded me that PostgreSQL also deals with network addresses, and I looked this up since a peer is currently migrating his LoB application away from MS-SQL-Server and said app deals with network addresses.

It turns out that PostgreSQL’s network address handling is a real cornucopia, with functions available to return stuff like the broadcast address, netmask, mask width or most-abbreviated representation of the network described (either directly or by an address within it).

It has two datatypes (three, if you count the MAC-address type), one (appropriately enough, cidr) for holding strict CIDR specifications and a another (inet) slightly looser type which will store and operate on an address and a mask a la Samba’s “interfaces” configuration directive.

PostgreSQL has so very many useful datatypes (geometry, spatial, etc) and such a plethora of operators and functions for them (e.g. the INTERVALs (including infinite intervals), OVERLAPS and EXTRACT operators in the time functions) that it’s sometimes very difficult to refrain from using them lest they need to be recreated for every other “lesser” database which the application might use. It’s richer than a chocolate mudcake in datatypes.

No comments: