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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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