I was wondering if there would be any way to do the following in PostgreSQL:

UPDATE cryptotable SET work = work + 'some big hexadecimal number'

where work is an unsigned 256 bit number. Right now my column is a character varying(64) column (hexadecimal representation) but I would be happy to switch to another data type if it lets me do the operation above.

If it's not possible with vanilla PostgreSQL, are there extensions that could help me?

(I also posted this to pgsql-hackers so check out the thread there for more ideas.)

  • 1
    It should be possible with the arbitrary precision numeric type, but performance of unpacking hex into a numeric in PL/PgSQL is likely to be absolutely horrifying. It might be worth writing a small C extension function to decode the hex into a numeric. – Craig Ringer Apr 10 '14 at 13:35
  • 1
    I wouldn't bother unpacking. In any procedural language, you may add the hex characters directly with a loop from right to left with carry propagation. In C, I'd bet any other method would be slower. – Daniel Vérité Apr 11 '14 at 10:28

Using pg-bignum

The pg-bignum library does what you want. I just patched it to accept hex input.

Download the library. Run

make
sudo make install

Now, run

CREATE EXTENSION bignum;

Now you can take a 256 bit number like this

59 51 a1 20 2e 65 62 26 ab f3 af e6 93 16 db 1c 
3b 37 24 47 75 32 72 95 79 e9 d7 fa 38 12 c0 0e 

And feed it to bn_hex_in(),

SELECT bn_in_hex('5951a1202e656226abf3afe69316db1c3b3724477532729579e9d7fa3812c00e');
                                   bn_in_hex                                   
-------------------------------------------------------------------------------
 40400070184923956455012790534029088594440775740385060328118871212830923866126
(1 row)

You can confirm that with Wolfram Alpha

Size advantages

Numeric is pretty big, from the docs

Numeric values are physically stored without any extra leading or trailing zeroes. Thus, the declared precision and scale of a column are maximums, not fixed allocations. (In this sense the numeric type is more akin to varchar(n) than to char(n).) The actual storage requirement is two bytes for each group of four decimal digits, plus three to eight bytes overhead.

The above example in numeric is 46 bytes (you can check with ::text::numeric), as a bignum it's 36.

I couldn't find a good answer to the underlying question of converting a very large hexadecimal number to a numeric type, so even though it's several years late, here's my alternative approach:

CREATE FUNCTION hex2numeric(hex TEXT)
RETURNS NUMERIC AS $$
DECLARE
  nibble RECORD;
  decimal_digit NUMERIC;
  result NUMERIC := 0;
BEGIN
  -- Loop through each hexadecimal digit, starting from the right
  FOR nibble IN (SELECT (n - 1) AS position, SUBSTRING(REVERSE(hex) FROM n FOR 1) AS digit FROM generate_series(1, length( hex ), 1) n) LOOP
    -- get the decimal representation of the hex digit
    decimal_digit := ('x' || nibble.digit)::BIT(4)::INT::NUMERIC;
    -- multiply the digit by the nth power of 16 and add to the result
    result := result + (decimal_digit  * (16::NUMERIC^nibble.position::NUMERIC));
  END LOOP;
  RETURN result;
END
$$ LANGUAGE plpgsql IMMUTABLE;

Usage (e.g. with a 512-bit hex number):

psql=# select hex2numeric('87783308B813E3B4FB3271534D844EE8FDBDF302273B3E3D5CB3BF6E0117F45DBAFE1AD7D30FA91CE7F0D33C7E877CA544D1770E7AD4F37691426E89806CB48B')::NUMERIC(156,0);

                                                                        hex2numeric
------------------------------------------------------------------------------------------------------------------------------------------------------------
 7095114927382653019461910892845641210567311772867384729122528908252679853572077146666940749408552370045395041242349820090839313239920717104090735323165835
(1 row)

Given that it's doing it digit-by-digit it's probably not very performant, but it gets the job done for my use case.

This is an extension of the method described here for converting hex digits to decimals and the algorithm for converting and summing hex digits as described here.

up vote 0 down vote accepted

I ended up simply using the numeric type as suggested and doing the hex/numeric conversion in my application code.

  • 1
    Why was this answer down voted? What's wrong with it? Better solutions? – guettli Mar 6 '15 at 10:36
  • @guettli dba.stackexchange.com/a/192746/2639 (though I didn't downvote) – Evan Carroll Dec 9 '17 at 0:57
  • This is apparently a valid answer, and it's encouraged to answer your own question. The downvote sounds unwarranted. – ghayes Jul 26 at 23:16

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.