Postgres 16 or newer

.. accepts non-decimal numeric constants (hexadecimal, octal, binary) in the form:

0xhexdigits
0ooctdigits
0bbindigits

Like other "Numeric constants", it defaults to the smallest numeric data type that won't overflow from the progression integer → bigint → numeric. So:

SELECT 0x1; -- 1 | integer SELECT 0x7fffffff; -- 2147483647 | integer SELECT 0x80000000; -- 2147483648 | bigint SELECT 0x7fffffffffffffff; -- 9223372036854775807 | bigint SELECT 0x8000000000000000; -- 9223372036854775808 | numeric 

This implicitly applies to the cast from text to int/bigint/numeric, so now you can:

SELECT ('0x'||'deadbeef')::numeric; 

Zegarek pointed that out in an added answer.

Postgres 15 or older

Use float as stepping stone

I only realized later that floating point types (float,real) support the same syntax at least since Postgres 9.4, probably longer:

SELECT '0xdeadbeef'::float; 

We can use that as stepping stone:

SELECT ('0x'||'deadbeef')::float::numeric; 

Simple and fast, but be wary of rounding errors with very big numbers. float can only represent up 15 decimal digits precisely. That translates to up to 12 hex digits reliably. Up to hex 38d7ea4c67fff, to be precise. Beyond that, least significant digits are set to 0. Also, Postgres 16 syntax is simpler and faster.

Original answer

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 digits

Up 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 digits

Up 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 

Related operations

Inverse

To convert back either integer or bigint, use the built-in (overloaded) function to_hex():

SELECT to_hex(3735928559); -- → 'deadbeef' 

uuid for max. 32 hex digits

The 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.

md5 hash

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:

• What is the optimal data type for an MD5 field?

pg-bignum

Internally, pg-bignum uses the SSL library for big numbers. This method has none of the drawbacks mentioned in the other answers with numeric. Nor is it slowed down by plpgsql. It's fast and it works with a number of any size. Test case taken from Erwin's answer for comparison,

CREATE EXTENSION bignum; SELECT hex, bn_in_hex(hex::cstring) FROM ( VALUES ('ff'::text) , ('7fffffff') , ('80000000') , ('deadbeef') , ('7fffffffffffffff') , ('8000000000000000') , ('ffffffffffffffff') , ('ffffffffffffffff123') ) t(hex); hex | bn_in_hex ---------------------+------------------------- ff | 255 7fffffff | 2147483647 80000000 | 2147483648 deadbeef | 3735928559 7fffffffffffffff | 9223372036854775807 8000000000000000 | 9223372036854775808 ffffffffffffffff | 18446744073709551615 ffffffffffffffff123 | 75557863725914323415331 (8 rows) 

You can get the type to numeric using bn_in_hex('deadbeef')::text::numeric.

Here is a version which uses numeric, so it can handle arbitrarily large hex strings:

create function hex_to_decimal(hex_string text) returns text language plpgsql immutable as $pgsql$ declare bits bit varying; result numeric := 0; exponent numeric := 0; chunk_size integer := 31; start integer; begin execute 'SELECT x' || quote_literal(hex_string) INTO bits; while length(bits) > 0 loop start := greatest(1, length(bits) - chunk_size); result := result + (substring(bits from start for chunk_size)::bigint)::numeric * pow(2::numeric, exponent); exponent := exponent + chunk_size; bits := substring(bits from 1 for greatest(0, length(bits) - chunk_size)); end loop; return trunc(result, 0); end $pgsql$; 

For example:

=# select hex_to_decimal('ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'); 32592575621351777380295131014550050576823494298654980010178247189670100796213387298934358015 

If anybody else is stuck with PG8.2, here is another way to do it.

bigint version:

create or replace function hex_to_bigint(hexval text) returns bigint as $$ select (get_byte(x,0)::int8<<(7*8)) | (get_byte(x,1)::int8<<(6*8)) | (get_byte(x,2)::int8<<(5*8)) | (get_byte(x,3)::int8<<(4*8)) | (get_byte(x,4)::int8<<(3*8)) | (get_byte(x,5)::int8<<(2*8)) | (get_byte(x,6)::int8<<(1*8)) | (get_byte(x,7)::int8) from ( select decode(lpad($1, 16, '0'), 'hex') as x ) as a; $$ language sql strict immutable; 

int version:

create or replace function hex_to_int(hexval text) returns int as $$ select (get_byte(x,0)::int<<(3*8)) | (get_byte(x,1)::int<<(2*8)) | (get_byte(x,2)::int<<(1*8)) | (get_byte(x,3)::int) from ( select decode(lpad($1, 8, '0'), 'hex') as x ) as a; $$ language sql strict immutable; 

Since PostgreSQL 16.0, it's enough to add '0x' in front of it and say it's a numeric:
_{demo at db<>fiddle}

select ('0x'||'123456789abcdef0123456789abcdef0123456789abcdef')::numeric; 

When typing (or injecting/interpolating when noone's looking), || and quotes ' can be skipped:

select 0x123456789abcdef0123456789abcdef0123456789abcdef; 

Works for octal and binary, too:

non-decimal integer constants are accepted in these forms:

0xhexdigits 0ooctdigits 0bbindigits 

where hexdigits is one or more hexadecimal digits (0-9, A-F), octdigits is one or more octal digits (0-7), and bindigits is one or more binary digits (0 or 1).

David Wolever's function hex_to_Decimal has a mistake. Here is the fixed version of the function:

create or replace function ${migrSchemaName4G}.hex_to_decimal(hex_string text) returns numeric language plpgsql immutable as $pgsql$ declare bits bit varying; result numeric := 0; exponent numeric := 0; chunk_size integer := 31; start integer; begin execute 'SELECT x' || quote_literal(hex_string) INTO bits; while length(bits) > 0 loop start := greatest(0, length(bits) - chunk_size) + 1; result := result + (substring(bits from start for chunk_size)::bigint)::numeric * pow(2::numeric, exponent); exponent := exponent + chunk_size; bits := substring(bits from 1 for greatest(0, length(bits) - chunk_size)); end loop; return result; end $pgsql$; 

There are many examples of the HEX translation into Number here, but most of them have a restriction on the length of HEX. I needed to solve the problem of a uniform distribution of identical records in different databases (Oracle and Postgres) on the "baskets". In this case, the same records in different databases should fall into the same "baskets" of processing. The identifier of the records is string.

Oracle has an ORA_HASH function. But there is no such function in Postgres. The problem was solved by using the same md5 caching functions, which generates a 32-symbol hex long. Only the above function helped, thanks to David Wolever.

For Oracle, the filtering condition turned out to be: MOD(TO_NUMBER(standard_hash(ID, 'MD5'), 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'), ${processCount}) = ${processNumber}

For Postgres - hex_to_Decimal(MD5(ID)) % ${Processcount} = ${Processnumber}

Here is another implementation:

CREATE OR REPLACE FUNCTION hex_to_decimal3(hex_string text) RETURNS numeric LANGUAGE plpgsql IMMUTABLE AS $function$ declare hex_string_lower text := lower(hex_string); i int; digit int; s numeric := 0; begin for i in 1 .. length(hex_string) loop digit := position(substr(hex_string_lower, i, 1) in '0123456789abcdef') - 1; if digit < 0 then raise '"%" is not a valid hexadecimal digit', substr(hex_string_lower, i, 1) using errcode = '22P02'; end if; s := s * 16 + digit; end loop; return s; end $function$; 

It is a straightforward one that works digit by digit, using the position() function to compute the numeric value of each character in the input string. Its benefit over hex_to_decimal2() is that it seems to be much faster (4x or so for md5()-generated hex strings).

Here is a poper way to convert hex to string... then you can check whether it's a numric type or not

SELECT convert_from('\x7468697320697320612076657279206C6F6E672068657820737472696E67','utf8') 

returns

this is a very long hex string 

Here is a other version which uses numeric, so it can handle arbitrarily large hex strings:

create OR REPLACE function hex_to_decimal2(hex_string text) returns text language plpgsql immutable as $pgsql$ declare bits bit varying; result numeric := 0; begin execute 'SELECT x' || quote_literal(hex_string) INTO bits; while length(bits) > 0 loop result := result + (substring(bits from 1 for 1)::bigint)::numeric * pow(2::numeric, length(bits) - 1); bits := substring(bits from 2 for length(bits) - 1); end loop; return trunc(result, 0); end $pgsql$; 

For example:

=# select hex_to_decimal('ffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffffff'); 32592575621351777380295131014550050576823494298654980010178247189670100796213387298934358015 

For example:

=# select hex_to_decimal('5f68e8131ecf80000'); 110000000000000000000 

CREATE OR REPLACE FUNCTION numeric_from_bytes(bytea) RETURNS numeric LANGUAGE plpgsql AS $$ declare bits bit varying; result numeric := 0; exponent numeric := 0; bit_pos integer; begin execute 'SELECT x' || quote_literal(substr($1::text,3)) into bits; bit_pos := length(bits) + 1; exponent := 0; while bit_pos >= 56 loop bit_pos := bit_pos - 56; result := result + substring(bits from bit_pos for 56)::bigint::numeric * pow(2::numeric, exponent); exponent := exponent + 56; end loop; while bit_pos >= 8 loop bit_pos := bit_pos - 8; result := result + substring(bits from bit_pos for 8)::bigint::numeric * pow(2::numeric, exponent); exponent := exponent + 8; end loop; return trunc(result); end; $$; 

In a future PostgreSQL version, when/if Dean Rasheed's patch 0001-Add-non-decimal-integer-support-to-type-numeric.patch gets committed, this can be simplified:

CREATE OR REPLACE FUNCTION numeric_from_bytes(bytea) RETURNS numeric LANGUAGE sql AS $$ SELECT ('0'||right($1::text,-1))::numeric $$;