Thread: hex to decimal and back again

Hi guys

I have some hex values which I am currently storing in varchar fields which
I would like to convert to decimal integers to make sorting and indexing
work correctly. I may need to convert them back to hex at some point for
display but this is not strictly necessary. I can seem to find the right
function to do this in postgres.

I can obviously do it in perl but thats not the best way.

Can someone give me pointers?

--

Peter Nixon
http://www.peternixon.net/
PGP Key: http://www.peternixon.net/public.asc

Peter Nixon wrote:
> I have some hex values which I am currently storing in varchar fields which
> I would like to convert to decimal integers to make sorting and indexing
> work correctly. I may need to convert them back to hex at some point for
> display but this is not strictly necessary. I can seem to find the right
> function to do this in postgres.
>

Is this what you need?

regression=# select x'ffff'::int4;
  int4
-------
  65535
(1 row)

HTH,

Joe

Peter Nixon wrote:
> Sort of. What I actually need to store is the following (From a Cisco VoIP
> radius call record)
>
> h323-incoming-conf-id = 2069659D 9A5BBD4F 8E0E99A7 42716F12
>
> I need to be able to JOIN on this field. I am storing it as text currently
> which works but is obviously less than efficient. Any ideas??
>
> radius=# select x'2069659D9A5BBD4F8E0E99A742716F12'::int8 ;
> ERROR:  Bit string is too large to fit in type integer
>
> radius=# select x'2069659D9A5BBD4F8E0E99A742716F12'::bigint ;
> ERROR:  Bit string is too large to fit in type int64
>
> I could break it into 4 fields I suppose, but I think that would make things
> complicated, and I was hoping to have a UNIQUE index on this field plus two
> others (Call time and IP Address), which would then make a unique index
> across 6 fields which I think would cause headaches for Postgres.
>
> Any help is appreciated..
>

Generally you should keep posts on the list because any individual may
or may not have the time/ideas/answers. Also, that way everyone gets the
benefit of the answers given, and the answers are stored in the archive
for others to make use of later.

In any case, you could use bytea to store these values. It would use 16
bytes plus 4 bytes overhead for storage. Bytea has index support, so it
should work fine for joins and unique constraints.

regression=# select decode('2069659D9A5BBD4F8E0E99A742716F12','hex');
                   decode
------------------------------------------
   ie\235\232[\275O\216\016\231\247Bqo\022
(1 row)

The octal escape sequences are only seen when outputting -- the data is
stored as pure binary. You can convert back to hex at any time using
encode():

regression=# create table radiusdata(id bytea);
CREATE TABLE
regression=# insert into radiusdata values
(decode('2069659D9A5BBD4F8E0E99A742716F12','hex'));
INSERT 1886255 1
regression=# create unique index idx1 on radiusdata(id);
CREATE INDEX
regression=# insert into radiusdata values
(decode('2069659D9A5BBD4F8E0E99A742716F12','hex'));
ERROR:  Cannot insert a duplicate key into unique index idx1
regression=# select * from radiusdata;
                     id
------------------------------------------
   ie\235\232[\275O\216\016\231\247Bqo\022
(1 row)

regression=# select encode(id, 'hex') from radiusdata;
               encode
----------------------------------
  2069659d9a5bbd4f8e0e99a742716f12
(1 row)

HTH,

Joe