Re: [HACKERS] Adding unsigned 256 bit integers
Thanks for helping me out everyone. I ended up simply using the numeric type (I didn't realize it could support such large numbers) and writing the hex-to-numeric conversion functions in my application code. On 11 April 2014 12:27, Leon Smith wrote: > pgmp is also worth mentioning here, and it's likely to be more efficient > than the numeric type or something you hack up yourself: > > http://pgmp.projects.pgfoundry.org/ > > Best, > Leon > > > On Thu, Apr 10, 2014 at 10:11 AM, k...@rice.edu wrote: > >> On Thu, Apr 10, 2014 at 09:13:47PM +0800, Olivier Lalonde wrote: >> > 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 integer. Right now my column is a >> > character varying(64) column (hexadecimal representation of the number) >> 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? >> > >> > -- >> > - Oli >> > >> > Olivier Lalonde >> > http://www.syskall.com <-- connect with me! >> > >> >> Hi Olivier, >> >> Here are some sample pl/pgsql helper functions that I have written for >> other purposes. They use integers but can be adapted to use numeric. >> >> Regards, >> Ken >> --- >> CREATE OR REPLACE FUNCTION hex2dec(t text) RETURNS integer AS $$ >> DECLARE >> r RECORD; >> BEGIN >> FOR r IN EXECUTE 'SELECT x'''||t||'''::integer AS hex' LOOP >> RETURN r.hex; >> END LOOP; >> END >> $$ LANGUAGE plpgsql IMMUTABLE STRICT; >> --- >> >> --- >> CREATE OR REPLACE FUNCTION bytea2int ( >> in_string BYTEA >> ) RETURNS INTEGER AS $$ >> >> DECLARE >> >> b1 INTEGER := 0; >> b2 INTEGER := 0; >> b3 INTEGER := 0; >> b4 INTEGER := 0; >> out_int INTEGER := 0; >> >> BEGIN >> >> CASE OCTET_LENGTH(in_string) >> WHEN 1 THEN >> b4 := get_byte(in_string, 0); >> WHEN 2 THEN >> b3 := get_byte(in_string, 0); >> b4 := get_byte(in_string, 1); >> WHEN 3 THEN >> b2 := get_byte(in_string, 0); >> b3 := get_byte(in_string, 1); >> b4 := get_byte(in_string, 2); >> WHEN 4 THEN >> b1 := get_byte(in_string, 0); >> b2 := get_byte(in_string, 1); >> b3 := get_byte(in_string, 2); >> b4 := get_byte(in_string, 3); >> END CASE; >> >> out_int := (b1 << 24) + (b2 << 16) + (b3 << 8) + b4; >> >> RETURN(out_int); >> END; >> $$ LANGUAGE plpgsql IMMUTABLE; >> --- >> >> >> -- >> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-hackers >> > > -- - Oli Olivier Lalonde http://www.syskall.com <-- connect with me! Freelance web and Node.js engineer Skype: o-lalonde
Re: [HACKERS] Adding unsigned 256 bit integers
pgmp is also worth mentioning here, and it's likely to be more efficient than the numeric type or something you hack up yourself: http://pgmp.projects.pgfoundry.org/ Best, Leon On Thu, Apr 10, 2014 at 10:11 AM, k...@rice.edu wrote: > On Thu, Apr 10, 2014 at 09:13:47PM +0800, Olivier Lalonde wrote: > > 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 integer. Right now my column is a > > character varying(64) column (hexadecimal representation of the number) > 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? > > > > -- > > - Oli > > > > Olivier Lalonde > > http://www.syskall.com <-- connect with me! > > > > Hi Olivier, > > Here are some sample pl/pgsql helper functions that I have written for > other purposes. They use integers but can be adapted to use numeric. > > Regards, > Ken > --- > CREATE OR REPLACE FUNCTION hex2dec(t text) RETURNS integer AS $$ > DECLARE > r RECORD; > BEGIN > FOR r IN EXECUTE 'SELECT x'''||t||'''::integer AS hex' LOOP > RETURN r.hex; > END LOOP; > END > $$ LANGUAGE plpgsql IMMUTABLE STRICT; > --- > > --- > CREATE OR REPLACE FUNCTION bytea2int ( > in_string BYTEA > ) RETURNS INTEGER AS $$ > > DECLARE > > b1 INTEGER := 0; > b2 INTEGER := 0; > b3 INTEGER := 0; > b4 INTEGER := 0; > out_int INTEGER := 0; > > BEGIN > > CASE OCTET_LENGTH(in_string) > WHEN 1 THEN > b4 := get_byte(in_string, 0); > WHEN 2 THEN > b3 := get_byte(in_string, 0); > b4 := get_byte(in_string, 1); > WHEN 3 THEN > b2 := get_byte(in_string, 0); > b3 := get_byte(in_string, 1); > b4 := get_byte(in_string, 2); > WHEN 4 THEN > b1 := get_byte(in_string, 0); > b2 := get_byte(in_string, 1); > b3 := get_byte(in_string, 2); > b4 := get_byte(in_string, 3); > END CASE; > > out_int := (b1 << 24) + (b2 << 16) + (b3 << 8) + b4; > > RETURN(out_int); > END; > $$ LANGUAGE plpgsql IMMUTABLE; > --- > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers >
Re: [HACKERS] Adding unsigned 256 bit integers
On Thu, Apr 10, 2014 at 09:13:47PM +0800, Olivier Lalonde wrote: > 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 integer. Right now my column is a > character varying(64) column (hexadecimal representation of the number) 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? > > -- > - Oli > > Olivier Lalonde > http://www.syskall.com <-- connect with me! > Hi Olivier, Here are some sample pl/pgsql helper functions that I have written for other purposes. They use integers but can be adapted to use numeric. Regards, Ken --- CREATE OR REPLACE FUNCTION hex2dec(t text) RETURNS integer AS $$ DECLARE r RECORD; BEGIN FOR r IN EXECUTE 'SELECT x'''||t||'''::integer AS hex' LOOP RETURN r.hex; END LOOP; END $$ LANGUAGE plpgsql IMMUTABLE STRICT; --- --- CREATE OR REPLACE FUNCTION bytea2int ( in_string BYTEA ) RETURNS INTEGER AS $$ DECLARE b1 INTEGER := 0; b2 INTEGER := 0; b3 INTEGER := 0; b4 INTEGER := 0; out_int INTEGER := 0; BEGIN CASE OCTET_LENGTH(in_string) WHEN 1 THEN b4 := get_byte(in_string, 0); WHEN 2 THEN b3 := get_byte(in_string, 0); b4 := get_byte(in_string, 1); WHEN 3 THEN b2 := get_byte(in_string, 0); b3 := get_byte(in_string, 1); b4 := get_byte(in_string, 2); WHEN 4 THEN b1 := get_byte(in_string, 0); b2 := get_byte(in_string, 1); b3 := get_byte(in_string, 2); b4 := get_byte(in_string, 3); END CASE; out_int := (b1 << 24) + (b2 << 16) + (b3 << 8) + b4; RETURN(out_int); END; $$ LANGUAGE plpgsql IMMUTABLE; --- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Adding unsigned 256 bit integers
On 04/10/2014 09:13 PM, Olivier Lalonde wrote: > I was wondering if there would be any way to do the following in PostgreSQL: > > UPDATE cryptotable SET work = work + 'some big hexadecimal number' For readers finding this in the archives, this question also appears here: http://dba.stackexchange.com/questions/62934/adding-unsigned-256-bit-integers-in-postgresql -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Adding unsigned 256 bit integers
On 04/10/2014 09:13 AM, Olivier Lalonde wrote: 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 integer. Right now my column is a character varying(64) column (hexadecimal representation of the number) 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? The numeric type allows numbers with huge numbers of digits. I've used it to calculate fibonacci numbers thousands of digits long. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
