Обсуждение: numeric to ipv6 inet

2013/8/23, Mikhail Puzanov <misha.puzanov@gmail.com>:
> Hello,
>
> I need to convert the set of ipv6 addresses stored as numerics
> (by historical reasons mostly) to inet type.
>
> Something like
> '0:0:0:0:0:0:0:0'::inet + 55831599345971591062080247067748335616::bigint
> apparently doesn't work as the number added is too big to be converted to
> bigint.
>
> Is there any reasonable way to do it in sql/pgplsql?
>


From manual:
bigint
8 bytes
large-range integer     -9223372036854775808 to 9223372036854775807

http://www.postgresql.org/docs/current/interactive/datatype-numeric.html#DATATYPE-INT

So 55831599345971591062080247067748335616 is out-of-range for a bigint.

Osvaldo

On Aug 23, 2013 11:58 AM, "Mikhail Puzanov" <misha.puzanov@gmail.com> wrote:
>
> Hello,
>
> I need to convert the set of ipv6 addresses stored as numerics
> (by historical reasons mostly) to inet type.
>
> Something like
> '0:0:0:0:0:0:0:0'::inet + 55831599345971591062080247067748335616::bigint
> apparently doesn't work as the number added is too big to be converted to bigint.
>
> Is there any reasonable way to do it in sql/pgplsql?

The only language I know that can easily work with such large ints is python. The only way to get the number to python is as text:

arjen=# create or replace function to_inet_ipv6(n varchar) returns inet language plpythonu as $$
import re
return re.sub('(....)', r'\1:', '%032x' % int(n))[:-1]
$$;
CREATE FUNCTION
arjen=# select to_inet_ipv6(55831599345971591062080247067748335615::varchar);
              to_inet_ipv6
-----------------------------------------
2a00:c65f:ffff:ffff:ffff:ffff:ffff:ffff
(1 row)

Groeten,

Arjen

>
> Thanks in advance.  
>
> --
> Best regards,
> Mikhail V. Puzanov.