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

On Mon, Mar 21, 2016 at 3:30 AM, 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.

postgres=# select encode('foo', 'hex');
 encode
────────
 666f6f
(1 row)

postgres=# select convert_from(decode('666f6f', 'hex'), 'utf8');
 convert_from
──────────────
 foo
(1 row)

merlin