# Generating a list of IPv6 addresses in PostgreSQL

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. :)