From: | Wei Shan <weishan(dot)ang(at)gmail(dot)com> |
---|---|
To: | Merlin Moncure <mmoncure(at)gmail(dot)com> |
Cc: | pgsql novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Re: Help with text(decimal) to hex conversion |
Date: | 2016-03-22 18:38:52 |
Message-ID: | CAFe9ZTo=3vRUOOxWatWfg7k0JO5d_xLNiqQ+HNtceg-EqKA97Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Hi all,
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;
encode
----------------------------------------
31303037373138303439353338363335343938
psql> select convert_from(decode('31303037373138303439353338363335343938',
'hex'), 'utf8');
convert_from
---------------------
1007718049538635498
Thanks!
On 22 March 2016 at 06:44, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> On Mon, Mar 21, 2016 at 3:30 AM, Wei Shan <weishan(dot)ang(at)gmail(dot)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
>
--
Regards,
Ang Wei Shan
From | Date | Subject | |
---|---|---|---|
Next Message | Paul Linehan | 2016-03-22 21:51:57 | Re: Tricky SQL problem - retrieve information_schema info and make use of it. |
Previous Message | Paul Linehan | 2016-03-22 15:46:02 | Re: Tricky SQL problem - retrieve information_schema info and make use of it. |