Postgresql: find next available IP addresses

November 5, 2011

Postgresql has the inet and cidr datatypes and some great functions for these types, very useful for storing and manipulating data for IP addresses and networks.

As a basic example, say you were storing hostnames and IP addresses, basic example table below:

If I wanted to add a new host then I would want to give it a unique IP address so I would have to find the next available IP address (defined as NOT already in this table) in order to allocate. Below is a query that generates a series of inets for the network passed and then looks to see if they are already in the table. An optional extra on the end is not selecting the first 10 or the last 5 IP address of a subnet – if you want to reserve these.

Just substitue the subnet and ip_table tablename;

 
SELECT sub.ip FROM
(SELECT set_masklen(((generate_series(1,
(2 ^ (32 - masklen('10.10.100.0/24'::cidr)))::integer - 2) +
'10.10.100.0/24'::cidr)::inet), 32) as ip) AS sub
WHERE sub.ip NOT IN
(SELECT ip_address from ip_table)
AND sub.ip > set_masklen('10.10.100.0/24', 32)+10
AND sub.ip < set_masklen(broadcast('10.10.100.0/24')::inet, 32)-5;

Here is the create function script to create a function to call for the above query.

 CREATE FUNCTION nextips_for(cidr) RETURNS SETOF inet AS
$$
SELECT sub.ip FROM (SELECT set_masklen(((generate_series(1, (2 ^ (32 -
masklen($1::cidr)))::integer - 2) +
$1::cidr)::inet), 32) as ip) AS sub
WHERE sub.ip NOT IN
(SELECT ip_address from ip_table)
AND sub.ip > set_masklen($1, 32)+10
AND sub.ip < set_masklen(broadcast($1)::inet, 32)-5;
$$ LANGUAGE SQL STABLE STRICT;

Again, just substitue the ip_table tablename for what you’re using;