Postgres Pro
Downloads
Home   >   mailing lists

Re: Help with text(decimal) to hex conversion - Mailing list pgsql-novice

From Szymon Lipiński
Subject Re: Help with text(decimal) to hex conversion
Date
Msg-id CAFjNrYuXByBzJyaq0bNvhutcXx9AavAx_TP0WfXmeW--uCY=+Q@mail.gmail.com
Whole thread Raw
In response to Help with text(decimal) to hex conversion  (Wei Shan <weishan.ang@gmail.com>)
List pgsql-novice
Tree view


On 21 March 2016 at 09:30, Wei Shan <weishan.ang@gmail.com> wrote:
Hi all,

There's a column that has datatype of text. The conversion will work fine for some of the rows.

psql> select to_hex(data::bigint)from table limit 5;
      to_hex
------------------
 499602d2
 499602d2
 4fa83d1136d920ef
 2e1b71785c8e11c
 53ff4c2824860fb8


However, for some records, the original data is too large for casting.

ERROR:  value "14481874327766585215" is out of range for type bigint

Any idea how to overcome this? to_hex function only accepts int or bigint.

Thanks!
--
Regards,
Ang Wei Shan


Hi,
try this function:

create or replace function text_to_hex(t text) returns text as $$
return hex(int('14481874327766585215'))[2:-1]
$$ language plpythonu;

select text_to_hex('14481874327766585215');
   text_to_hex    
──────────────────
 c8f9f3f90c604f7f
(1 row)



--
    regards Szymon Lipiński

pgsql-novice by date:

Previous
From: Gavin Flower
Date:
Subject: Re: Help with text(decimal) to hex conversion
Next
From: Merlin Moncure
Date:
Subject: Re: Help with text(decimal) to hex conversion