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;