Adding unsigned 256 bit integers in PostgreSQL

Question

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

Answer 1

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.

Answer 2

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

Answer 3

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.