Re: Help with text(decimal) to hex conversion

Please don't top-post.

On Tue, Mar 22, 2016 at 11:38 AM, Wei Shan <weishan(dot)ang(at)gmail(dot)com> wrote:

> Hi all,
>
> The method suggested so far doesn't actually work.
>

​What "method"(s) have you tried?- all you show below are
"to_hex(...::bigint)"

> I'm trying to convert a text datatype to hex. The maximum hex returned
> will be 20 octets. I'm hoping to do it within PostgreSQL so I can call it
> like a function (to_hex).
>

​PostgreSQL, from what I can tell, cannot natively (i.e, in SQL or
pl/pgsql) support any number larger than bigint's​ maximum value. If you
need to handle something larger you must *keep the representation as a
string* and provide that string to a programming language number library
that can. Supposedly both Java and Python can.

> psql> select column from table limit 5;
> ---------------------
> 4849018213204493635
> 4939764883475860925
> 1006304053701792827
> 1007718049538635498
> 1010517297675790156
> (5 rows)
>
>
> psql> select to_hex(column_name::bigint) from tablelimit 5;
> to_hex
> ------------------
> 434b2c5fab740543
> 448d91fd51870dbd
> df71c3488ee543b
> dfc223a187ff6ea
> e061420d75a674c
> (5 rows)
>

​column != column_name ...​ and again you must not cast to bigint.

> I also tried the following suggestions.
>
> psql> select encode(column::bytea,'hex') from table limit 1;
> encode
> ----------------------------------------
> 31303037373138303439353338363335343938
>
> psql> select convert_from(decode('31303037373138303439353338363335343938',
> 'hex'), 'utf8');
> convert_from
> ---------------------
> 1007718049538635498
>
>
​This is Row #4 in your data...​but you are simply encoding the textual
representation of something that looks like a number, then decoding it
again.

SELECT encode('16'::bytea, 'hex'); a change of numeric base would give you
"F", not "3136"

David J.