Adding unsigned 256 bit integers

Started by Olivier Lalondealmost 12 years ago6 messages

olalonde@gmail.com

almost 12 years ago

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!

Freelance web and Node.js engineer
Skype: o-lalonde

Andrew Dunstan

andrew@dunslane.net

almost 12 years ago

In reply to: Olivier Lalonde (#1)

Re: 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

Craig Ringer

craig@2ndquadrant.com

almost 12 years ago

In reply to: Olivier Lalonde (#1)

Re: 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

ktm@rice.edu

almost 12 years ago

In reply to: Olivier Lalonde (#1)

Re: 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

Leon Smith

leon.p.smith@gmail.com

almost 12 years ago

In reply to: ktm@rice.edu (#4)

Re: 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, ktm@rice.edu <ktm@rice.edu> wrote:

Show quoted text

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

Olivier Lalonde

olalonde@gmail.com

over 11 years ago

In reply to: Leon Smith (#5)

Re: 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 <leon.p.smith@gmail.com> 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, ktm@rice.edu <ktm@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