Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.postgresql > #641

Re: how to count the number of ipv6 addresses in a range?

Show all headers | View raw

On 30.03.2015 22:13, M. Strobel wrote:
> On 30.03.2015 18:43, Mateusz Viste wrote:
>> Hello group,
>>
>> Today I got into troubles with an old piece of code that was working very well on an
>> "inet" column when the column was used for IPv4 addresses, but went south with
>> introduction of IPv6 records.
>>
>> Basically, it's about counting the number of IP addresses in a range:
>>
>>  > postgres=# select 'a:b:c:dd::'::inet - 'a:b:c:dd::'::inet;
>>  >  ?column?
>>  > ----------
>>  >         0
>>  > (1 row)
>>
>> The above works fine, but if the range becomes greater, then postgres is happy no
>> more:
>>
>> postgres=# select 'a:b:c:dd::'::inet - 'a:b:c:da::'::inet;
>> ERROR:  result is out of range
>> postgres=#
>>
>> Question to you guys - is there some "clean" way to deal with this? I naively tried
>> casting the result in such way:
>>
>>  > select ('a:b:c:dd::'::inet - 'a:b:c:da::'::inet)::NUMERIC(30);
>>
>> But this won't help. Any kind ideas? I'm running a Postgres 8.4 cluster, but tested
>> on a v9.3.2 with same sad results.
>>
>> cheers,
>> Mateusz
>
> well, looking at
>
>      'a:b:c:dd::'::inet - 'a:b:c:da::'::inet
>
> let's find out the size of the difference.
>
> The prefix aa:b:c:dd is 32 bits long, subtract this from 128 to get a remaining
> length of 96 bits.
>
> Add to this the difference 0xd - 0xa, so the result has at least 98 bits. This can be
> handled only with bignum support.
>

Just for fun: the earth surface is 510072000 km^2, so with 98 bit you would cover one 
square meter of earth surface with:

Welcome to Racket v6.1.
-> ( / (expt 2 98) 510072000000000)
77371252455336267181195264/124529296875 

-> (exact->inexact ( / (expt 2 98) 510072000000000)) 

621309638751112.2 

->

621309638 million data points I think

(calculating with scheme because of automatic integer conversion to bignums)

/Str.

Back to comp.databases.postgresql | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

how to count the number of ipv6 addresses in a range? Mateusz Viste <mateusz.viste@localhost> - 2015-03-30 18:43 +0200
  Re: how to count the number of ipv6 addresses in a range? "M. Strobel" <strobel@example.com> - 2015-03-30 22:13 +0200
    Re: how to count the number of ipv6 addresses in a range? "M. Strobel" <strobel@example.com> - 2015-03-31 01:25 +0200
    Re: how to count the number of ipv6 addresses in a range? Mateusz Viste <mateusz.viste@localhost> - 2015-03-31 09:39 +0200
      Re: how to count the number of ipv6 addresses in a range? "M. Strobel" <strobel@example.com> - 2015-03-31 09:56 +0200
        Re: how to count the number of ipv6 addresses in a range? Mateusz Viste <mateusz.viste@localhost> - 2015-03-31 11:03 +0200
      Re: how to count the number of ipv6 addresses in a range? Ralf Döblitz <doeblitz@doeblitz.net> - 2015-04-02 16:48 +0000
        Re: how to count the number of ipv6 addresses in a range? Mateusz Viste <mateusz.viste@localhost> - 2015-04-03 09:09 +0200
          Re: how to count the number of ipv6 addresses in a range? Ralf Döblitz <doeblitz@doeblitz.net> - 2015-04-03 13:17 +0000

csiph-web