Next available IP/Network with PostgreSQL

Get next available networks/IPs.
You’ll need to have a table with used networks/IPs defined similar to the one bellow.

CREATE TABLE ip (
    id SERIAL NOT NULL CONSTRAINT ip_pkey PRIMARY KEY,
    remote_addr CIDR NOT NULL CONSTRAINT ip_remote_addr UNIQUE
);

How it works

  • generates series of networks of length new_ip_mask_len inside the given ip_address/mask network and checks if they are available. This means there are no other (sub)network of the generated ones inside the ip table AND there is not a network with the same prefix (even a bigger one) @see last where clause
  • hardcoded vars are as example, use ? bind variables from your ORM instead
  • for a single IP set the mask new_ip_mask_len to 32, duh

The SQL code

SELECT new.addr FROM ( 
    WITH vars AS (
        SELECT 
            '193.203.0.0'::varchar ip_address, 
            '24'::integer mask, 
            '29'::integer new_ip_mask_len
    ),   vals AS (
        SELECT
            new_ip_mask_len,
            ( host(network((vars.ip_address || '/' || vars.mask)::inet)) || '/' || vars.mask )::cidr AS subnet,
            ( 2 ^ ( 32 - mask ) )::integer - 2  AS max,
            ( 2 ^ ( 32 - new_ip_mask_len ) )::integer AS step
        FROM vars
    )
    SELECT set_masklen(
        (generate_series(0, max, step) + subnet)::inet,
        new_ip_mask_len
    ) AS addr FROM vals
) AS new
WHERE NOT EXISTS(
    SELECT ip.id FROM ip ip 
    WHERE new.addr >>= ip.remote_addr OR host(new.addr) = host(ip.remote_addr)
);

Leave a Reply

Your email address will not be published. Required fields are marked *