Postgresql: find next available IP addresses

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. Here is how to query for the next sequentially available IP address in a given table.

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

CREATE TABLE ip_table (
    id serial NOT NULL,
    ip_address inet NOT NULL,
    hostname character varying(25) NOT NULL
);

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;