The method suggested so far doesn't actually work.
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).
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)
I also tried the following suggestions.
psql> select encode(column::bytea,'hex') from table limit 1;
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.
