Subnetting IPv6 in Postgres

Question

I am using postgres 9.5, and I was wondering if postgres allows for any type of subnetting or calculations on IP addresses, specifically IPv6. I am aware they have the cidrformat for storing data like IpV4 and Ipv6 formats. However, can I do anything like find out how many client IP's are in the subnet stored in the DB?

For example. If I stored the IPv6 address of 2001:dd8::/64, could I find out how many host IPs there are (18446744073709551616, according to some calculators).

My guess is that the answer is NO, postgres does not support this.

Answer 1

As 3manuek mentioned, there are functions and operators to deal with IP addresses - be it IPv4 or IPv6.

I am wondering a bit where your number comes from. The address range you gave translates to

2001:0dd8:0000:0000:0000:0000:0000:0000

which is an address range of length 1.

If you meant all possible addresses starting with '2001:dd8:', then the correct designation of this range would be 2001:dd8::/32, as it is the first 32 bits that are given. According to a calculator tool, the resulting range is the following:

Start Range: 2001:dd8:0:0:0:0:0:0
End Range: 2001:dd8:ffff:ffff:ffff:ffff:ffff:ffff
No. of host: 79228162514264337593543950336

Meanwhile, if you omit the block size (/32), you get a single address (the one I showed above).

So, there is a way to get the number provided by the calculator, using builtin PostgreSQL functionality. Our friend here is masklen():

SELECT round(2 ^ (128 - masklen('2001:dd8::/32'::cidr))::numeric);

             round             
───────────────────────────────
 79228162514264337593543950336

The result is the same as form the calculator. The rounding is there for removing the decimal point and the zeros after it, which are the result of casting to numeric.

Answer 2

Well, Postgres supports Network Operators and Functions and IPv6 isn't the exception. Actually your question is a good one, as I didn't see much examples around on how to do this (explicitly with IPv6). Here is an example on how you can do that:

postgres=# SELECT '2001:0DD8::1428:57AB/128'::cidr <<   
           '2001:dd8::/32'::inet;
----------
  t

The << operator corresponds to the is contained by. The above example does only the inline comparison, but it can be used as an usual operator like in the following query:

SELECT id FROM IPv6s WHERE theIp << '2001:dd8::/32'::cidr;

I made an small example at this SQLFiddle link.