I am trying to convert hex to decimal using PostgreSQL 9.1
with this query:
SELECT to_number('DEADBEEF', 'FMXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
I get the following error:
ERROR: invalid input syntax for type numeric: " "
What am I doing wrong?
The conversion of hexadecimal to decimal is done by using the base number 16. The hexadecimal digit is expanded to multiply each digit with the power of 16. The power starts at 0 from the right moving forward towards the right with the increase in power. For the conversion to complete, the multiplied numbers are added.
The maximum 4-digit hexadecimal number is FFFF16 which is equal to 65,535 in decimal and so on as the number of digits increase.
In order to use this ascii text to decimal converter tool, type an ascii value like "love" to get "108 111 118 101" and then hit the Convert button. This is the way you can convert up to 128 ascii text to decimal characters.
There is no cast from hex numbers in text
representation to a numeric type, but we can use bit(n)
as waypoint. There are undocumented casts from bit strings (bit(n)
) to integer types (int2
, int4
, int8
) - the internal representation is binary compatible. Quoting Tom Lane:
This is relying on some undocumented behavior of the bit-type input converter, but I see no reason to expect that would break. A possibly bigger issue is that it requires PG >= 8.3 since there wasn't a text to bit cast before that.
integer
for max. 8 hex digitsUp to 8 hex digits can be converted to bit(32)
and then coerced to integer
(standard 4-byte integer):
SELECT ('x' || lpad(hex, 8, '0'))::bit(32)::int AS int_val FROM ( VALUES ('1'::text) , ('f') , ('100') , ('7fffffff') , ('80000000') -- overflow into negative number , ('deadbeef') , ('ffffffff') , ('ffffffff123') -- too long ) AS t(hex);
int_val ------------ 1 15 256 2147483647 -2147483648 -559038737 -1
Postgres uses a signed integer type, so hex numbers above '7fffffff'
overflow into negative integer numbers. This is still a valid, unique representation but the meaning is different. If that matters, switch to bigint
; see below.
For more than 8 hex digits the least significant characters (excess to the right) get truncated.
4 bits in a bit string encode 1 hex digit. Hex numbers of known length can be cast to the respective bit(n)
directly. Alternatively, pad hex numbers of unknown length with leading zeros (0
) as demonstrated and cast to bit(32)
. Example with 7 hex digits and int
or 8 digits and bigint
:
SELECT ('x'|| 'deafbee')::bit(28)::int , ('x'|| 'deadbeef')::bit(32)::bigint;
int4 | int8 -----------+------------ 233503726 | 3735928559
bigint
for max. 16 hex digitsUp to 16 hex digits can be converted to bit(64)
and then coerced to bigint
(int8
, 8-byte integer) - overflowing into negative numbers in the upper half again:
SELECT ('x' || lpad(hex, 16, '0'))::bit(64)::bigint AS int8_val FROM ( VALUES ('ff'::text) , ('7fffffff') , ('80000000') , ('deadbeef') , ('7fffffffffffffff') , ('8000000000000000') -- overflow into negative number , ('ffffffffffffffff') , ('ffffffffffffffff123') -- too long ) t(hex);
int8_val --------------------- 255 2147483647 2147483648 3735928559 9223372036854775807 -9223372036854775808 -1 -1
uuid
for max. 32 hex digitsThe Postgres uuid
data type is not a numeric type. But it's the most efficient type in standard Postgres to store up to 32 hex digits, only occupying 16 bytes of storage. There is a direct cast from text
to uuid
(no need for bit(n)
as waypoint), but exactly 32 hex digits are required.
SELECT lpad(hex, 32, '0')::uuid AS uuid_val FROM ( VALUES ('ff'::text) , ('deadbeef') , ('ffffffffffffffff') , ('ffffffffffffffffffffffffffffffff') , ('ffffffffffffffffffffffffffffffff123') -- too long ) t(hex);
uuid_val -------------------------------------- 00000000-0000-0000-0000-0000000000ff 00000000-0000-0000-0000-0000deadbeef 00000000-0000-0000-ffff-ffffffffffff ffffffff-ffff-ffff-ffff-ffffffffffff ffffffff-ffff-ffff-ffff-ffffffffffff
As you can see, standard output is a string of hex digits with typical separators for UUID.
This is particularly useful to store md5 hashes:
SELECT md5('Store hash for long string, maybe for index?')::uuid AS md5_hash;
md5_hash -------------------------------------- 02e10e94-e895-616e-8e23-bb7f8025da42
See:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With