PostgreSQL has fantastic native support for both IPv4 & IPv6 addressing, and we can use some convenient built-in methods to generate IP ranges.

For instance, since IPv4 addresses are stored as 32 bit integers, the inet type in PostgreSQL is smart enough to allow for simple addition and subtraction:

SELECT '192.0.2.1'::inet + 1; -- 192.0.2.2

The same is true for IPv6 addresses:

SELECT '2001:db8::1/32'::inet + 1; -- 2001:db8::2/32

In IPv4, to generate a list of IPs, we might use generate_series() to help generate a list of all IPs in the 192.0.2.0/24 subnet:

SELECT '192.0.2.0'::inet + g FROM generate_series(0, 255) g;

  ?column?
-------------
 192.0.2.0
 192.0.2.1
 192.0.2.2
 192.0.2.3
 .. and so on
 192.0.2.253
 192.0.2.254
 192.0.2.255
(256 rows)

This is a good approach for single hosts. What about splitting bigger subnets down to smaller subnets? To split 192.0.2.0/24 into /30s, you could reach for pow():

SELECT
    '192.0.2.0/24'::inet + (g * pow(2, 32-30)::int)
FROM
    generate_series(0, pow(2, 30-24)::int - 1) g;

    ?column?
----------------
 192.0.2.0/24
 192.0.2.4/24
 192.0.2.8/24
 .. and so on
 192.0.2.244/24
 192.0.2.248/24
 192.0.2.252/24
(64 rows)

That works, but the CIDR mask length is /24 instead of our wanted /30. set_masklen() fixes that:

SELECT
    set_masklen(
        '192.0.2.0/24'::inet + (g * pow(2, 32-30)::int),
        30
    )
FROM
    generate_series(0, pow(2, 30-24)::int - 1) g;

  set_masklen
----------------
 192.0.2.0/30
 192.0.2.4/30
 192.0.2.8/30
 .. and so on
 192.0.2.244/30
 192.0.2.248/30
 192.0.2.252/30
(64 rows)

In IPv6 however, given its vast 128 bit addressing size, our typical use-case would almost always be to generate a list of networks carved out of a bigger network.

For example, to generate a list of /48s inside a /40 (remember there are 256 /48s in a /40), we could attempt a similar approach to IPv4:

SELECT
    '2001:db8::/40'::inet + (g * pow(2, 128 - 48)::bigint)
FROM
    generate_series(0, 255) g;

ERROR:  bigint out of range

Thwarted! To go from 2001:db8::/48 to the next range of 2001:db8:1::/48 we would need to add 2^80 (80 bits) which overflows the 64 bit bigint type.

How can we avoid overflowing the bigint type, but still achieve what we want? I mentioned PostgreSQL has fanastic support native support, and fortunately enough, it provides a broadcast() function which allows us to bypass adding a very large integer ourselves. We use it like so:

SELECT broadcast('2001:db8::/48');

               broadcast
----------------------------------------
 2001:db8:0:ffff:ffff:ffff:ffff:ffff/48
(1 row)

That’s handy! Combining that with integer addition, we can get the next range easily enough:

SELECT broadcast('2001:db8::/48') + 1;

    ?column?
-----------------
 2001:db8:1::/48
(1 row)

Another useful feature of PostgreSQL’s network types are the “is subnet contained” operators, << and <<=. They allow us to check whether an IP or a range is contained by another range. For example, for the <<= operator (contained or equal to):

-- smaller subnet inside a larger one
SELECT '2001:db8::/48'::inet <<= '2001:db8::/40'::inet;
 ?column?
----------
 t
(1 row)

-- the subnets are equal
SELECT '2001:db8::/48'::inet <<= '2001:db8::/48'::inet;
 ?column?
----------
 t
(1 row)

and for the more strict << operator (contained):

-- smaller subnet inside a larger one
SELECT '2001:db8::/48'::inet << '2001:db8::/40'::inet;
 ?column?
----------
 t
(1 row)

-- the subnets are equal
SELECT '2001:db8::/48'::inet << '2001:db8::/48'::inet;
 ?column?
----------
 f
(1 row)

Using a few things we’ve learnt, we can combine it all into a recursive common table expression (CTE) to generate our range:

WITH RECURSIVE cte AS (
    SELECT set_masklen('2001:db8::/40'::inet, 48) AS ip
    UNION SELECT broadcast(ip) + 1 FROM cte WHERE broadcast(ip) + 1 <<= '2001:db8::/40'::inet
)
SELECT ip FROM cte

        ip
------------------
 2001:db8::/48
 2001:db8:1::/48
 2001:db8:2::/48
 .. and so on
 2001:db8:fd::/48
 2001:db8:fe::/48
 2001:db8:ff::/48
(256 rows)

Note: it’s easy to generate massive ranges of IPs with this, so use it carefully. PostgreSQL will definitely not generate us a list of /128s that belong to a /0, for example. :)