Question 89669 in PostgreSQL Question posted in
The official documentation can be found here.

How to use postgres to cast a 64-char / 256-bit hex-string to a numeric(78,0)? – Postgresql

Trying to translate a uint256 number which is encoded as a 64-char / 256-bit hex.
Eventually the result should be stored in a numeric(78,0) which is enough to encode an uint256.

For smaller hex-strings there’s pretty direct ways to encode to int4/int8 but this won’t do here.

Therefore trying to chunk the hex up in 4 64-bit parts, which can all be represented by a int8. Then glue it up with some bitshifting and done.

Unfortunately didn’t realise int8 is signed so that gives me wrong results (code below).
I guess I could chunk the hex up in smaller parts and make it work with a lot of hassle, but by now I’m wondering that there must somehow be a better way. Anyone?

-- 1. Split 256 bits into 4 seperate parts which are represented by int8's which can all hold 64 bits (4 * 64 = 256)
-- 2. cast int8 to numeric(78,0) which can hold a uint256, and some bitshifting (but not really since not supported by numeric) to get all parts
-- 3. add up the parts to arrive at the final number
--
-- WRONG RESULT: since int8 are signed. 
select 
 val_a + val_b + val_c + val_d as value  
from(
     select 
        -- concat('x', substr(value, 0, 64))::bit(256) value_as_bits, -- all data as 256 bits
        concat('x', substr(value, 0, 16))::bit(64)::int8::numeric(78,0) * 2^48::numeric(78,0) val_a,
        concat('x', substr(value, 0 + 16, 16))::bit(64)::int8::numeric(78,0) * 2^32::numeric(78,0) val_b,
        concat('x', substr(value, 0 + 32, 16))::bit(64)::int8::numeric(78,0) * 2^16::numeric(78,0) val_c, 
        concat('x', substr(value, 0 + 48, 16))::bit(64)::int8::numeric(78,0) val_d -- '-8257646570878140416' issue here since int8 is not unsigned
    from (
        select '000000000000000000000000000000000000000015b19218d66f231d61600000' as value -- hex representation of an unsigned 256 bit number
    ) as x
 
) as x;
;
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    To answer my own question:

    -- Val is 64-char hex. Transform to Uint256 represented as a numeric(78,0)
CREATE OR REPLACE FUNCTION uint256_hash_to_numeric (val text) RETURNS numeric AS $$ 
    select val_a + val_b + val_c + val_d + val_e
    from(
         select 
            concat('x00000000', substr(value, 1, 8))::bit(64)::int8::numeric(78,0) * 2^224::numeric(78,0) val_a,
            concat('x00', substr(value, 9, 14))::bit(64)::int8::numeric(78,0) * 2^168::numeric(78,0) val_b,
            concat('x00', substr(value, 23, 14))::bit(64)::int8::numeric(78,0) * 2^112::numeric(78,0) val_c,
            concat('x00', substr(value, 37, 14))::bit(64)::int8::numeric(78,0) * 2^56::numeric(78,0) val_d,
            concat('x00', substr(value, 51, 14))::bit(64)::int8::numeric(78,0) val_e 
        from (select val as value) as x
    ) as x
$$ LANGUAGE SQL;

    I'll be performance testing this and will leave open in the meantime


  2. 0

    Introduction

    You can do something like

    ('x'||lpad(the_hex_value,16,'0'))::bit(64)::bigint

    The following sections will cover cases when even bigint would not be enough.

    Sample schema

    create table temp(
    pk serial primary key,
    ch char(1),
    fk int,
    ind int
);

insert into temp(ch, fk)
select unnest(
    string_to_array(
        regexp_replace('0123456789ABCD', E'(.)(?!$)', E'\1 ', 'g'),
        ' '
    )
), 1;

insert into temp(ch, fk)
select unnest(
    string_to_array(
        regexp_replace('123', E'(.)(?!$)', E'\1 ', 'g'),
        ' '
    )
), 2;


update temp tmp
set ind = (select count(*) from temp tmp2 where tmp2.fk = tmp.fk and tmp2.pk > tmp.pk);

    Explanation:

    • we create a table called temp
      • pk is a primary key which will be useful later to determine the position of each digit
      • ch is a char representing the hexa digit
      • fk is a foreign key, a one-way vertex towards the original table
      • ind is an initially uninitialized field, which will turn out to be the inverted digit index in order to be able to determine which power of 16 should we multiply the numeric value of ch with
    • we fill this temp table with two rows, using some hard-coded values in our example both for the actual hexa number and the foreign key
    • we update temp in order to compute and store the appropriate value of ind

    The query

    select fk, sum(digit)
from (
select fk, case
    when ch = 'A' then 10
    when ch = 'B' then 11
    when ch = 'C' then 12
    when ch = 'D' then 13
    when ch = 'E' then 14
    when ch = 'F' then 15
    else ch::int
end
* power(16, RANK() OVER(ORDER BY ind) - 1) as digit
from temp) t
group by fk;

    Explanation:

    • we convert ch into a numerical value
    • and multiply it with 16^(one less than the inverted index)
    • we group by fk in order to make sure that we get the individual value for each fk
    • finally we sum the computed representative digit value for each fk group

    Note, here we sum the values. If you experience any problems with this, feel free to convert the digits into textual and concat them with string_agg. You should aim to the simpler approach if possible and resort to this one only if any easier approach fails.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search