Storing network information

Given my experience in my daily work as a PostgreSQL professional I have the impression that most people are still not aware of the fact that PostgreSQL can handle IP addresses – and network information in general – pretty nicely.

But, there are actually two data types, which can help users to store and verify network data: cidr and inet. Here are some examples:

test=# SELECT '192.168.0.45/24'::inet;

inet

-----------------

192.168.0.45/24

(1 row)

test=# SELECT '192.168.0.45'::cidr;

cidr

-----------------

192.168.0.45/32

(1 row)

The inet type accepts an optional netmask. It is perfect to store entire IP ranges. The cidr type will only hold a single IP address.

The beauty of validation

Just like every data type in PostgreSQL, cidr and inet will validate their content nicely:

test=# SELECT '192.168.0.450'::cidr;

ERROR: invalid input syntax for type cidr: "192.168.0.450"

LINE 1: SELECT '192.168.0.450'::cidr;

Validating data directly is important if you want to keep bad data our of your system.

Some little math

Did you know that you can actually do math in PostgreSQL? You can add and subtract numbers from an IP range easily. This is very nice if you want to find the next spare IP address or whatever.

Here is how it works:

test=# SELECT '192.168.0.45'::cidr + 22;

?column?

--------------

192.168.0.67

(1 row)

test=# SELECT '192.168.0.45'::cidr + 220;

?column?

-------------

192.168.1.9

(1 row)

test=# SELECT '192.168.0.45'::cidr - 220;

?column?

----------------

192.167.255.81

(1 row)

By the way: This kind of magic will also work with IPv6 IPs:

test=# SELECT '::1'::cidr + 22;

?column?

----------

::17

(1 row)

Visit us on facebook: www.fb.com/cybertec.postgresql

Hans-Juergen Schoenig
Hans-Jürgen Schönig has 15 years of experience with PostgreSQL. He is consultant and CEO of the company „Cybertec Schönig & Schönig GmbH“ (www.cybertec.at, www.postgresql-support.de), which has served countless customers around the globe.