Adding unsigned 256 bit integers in PostgreSQL

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

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.

StackExchange Database Administrators Q#62934, answer score: 5