postgres的10进制与16进制互相转换
1.10进制转16进制
postgres里面有一个内置的10进制转16进制的函数:to_hex(int)/to_hex(bigint)
[postgres@localhost ~]$ psql
password:
psql (9.1.3)
type help for help.
postgres=# select to_hex(9);
to_hex
--------
9
(1 row)
postgres=# select to_hex(17);
to_hex
--------
11
(1 row)
postgres=# select to_hex(31);
to_hex
--------
1f
(1 row)
postgres=# select to_hex(255);
to_hex
--------
ff
(1 row)
postgres=# select to_hex(256);
to_hex
--------
100
(1 row)
2.16进制转10进制
没有内置的，参考网上的一个例子
postgres=# create function
hex_to_dec(in_hex text)
returns int
immutable strict language sql as
$body$
select cast(cast(('x' || cast($1 as text)) as bit(8)) as int);
$body$;
create function
postgres=# select hex_to_dec('1f');
hex_to_dec
------------
31
(1 row)
postgres=# select hex_to_dec('ff');
hex_to_dec
------------
255
(1 row)
postgres=# select hex_to_dec('fe');
hex_to_dec
------------
254
(1 row)
postgres=# select hex_to_dec('09');
hex_to_dec
------------
9
(1 row)
postgres=# select hex_to_dec('11');
hex_to_dec
------------
17
(1 row)
内置的函数说明,支持输入是int或者bigint类型
to_hex(int/bigint):
create or replace function to_hex(integer)
returns text as
'to_hex32'
language internal immutable strict
cost 1;
alter function to_hex(integer)
owner to postgres;
comment on function to_hex(integer) is 'convert int4 number to hex';
create or replace function to_hex(bigint)
returns text as
'to_hex64'
language internal immutable strict
cost 1;
alter function to_hex(bigint)
owner to postgres;
comment on function to_hex(bigint) is 'convert int8 number to hex';