XOR between numeric in PostgreSQL

Question

So I have two integers > 2 ^ 63 - 1 but < 2 ^ 64 -1. This is BIGINT UNSIGNED in most languages, but according to Comparison of database column types in MySQL, PostgreSQL, and SQLite? (Cross-Mapping), its numeric(20) in postogresql

problem is, when I try to do a postgresql xor operation between two unsigned bigints:

select 17418945696623429624::numeric(20) # 17418945696623429624::numeric(20);

=>

ERROR:  operator does not exist: numeric # numeric

with any non "numeric" integer less than 2 ^ 63 -1, it works fine.

rephrase: How can I perform a XOR operation between two numbers larger than 2 ^ 63 - 1 but less than 2 ^ 64 - 1? Ex. How can I XOR 17418945696623429624 with itself in PostgreSQL?

Andomar · Accepted Answer · 2011-03-28 19:33:13Z

1

Looks like PostgreSQL has a bigint type-- why don't you use that?

answered Mar 28, 2011 at 19:33

Andomar

239k55 gold badges387 silver badges412 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

user545139 Over a year ago

numbers like 17418945696623429624, which I need to use are larger than a bigint (2 ^ 63 - 1). I either need an unsigned bigint (which postgre doesn't support), or a numeric(20).

Andomar Over a year ago

For XOR, it doesn't matter if you're signed or unsigned. You could store the number as a bigint, and convert it to unsigned client side.

user545139 Over a year ago

no i mean bigint is not big enough to store the integer I want to store. UNSIGNED BIGINT can store a larger integer than BIGINT and it is the data type im using in the MySQL version of my db.

Andomar Over a year ago

@Shea Barton: You can convert the unsigned bigint to a signed bigint before you store it in the database. It'll look like a negative number, but it will XOR fine.

nate c Over a year ago

You can use bit strings also. Both support at least 64 bit bit strings.

Grzegorz Szpetkowski · Accepted Answer · 2011-03-28 19:59:14Z

1

According to PostgreSQL documentation:

The bitwise operators work only on integral data types, whereas the others are available for all numeric data types.

numeric belongs to Arbitrary Precision Numbers (not Integer Types), so XOR operator not works.

There is (only signed) bigint integer type, but it's only -2^63 to 2^63 - 1. Unfortunately there is no unsigned bigint datatype (i.e. 0 to 2^64 -1).

I think that it could be possible to implement such calculation using bigint datatype with some "bitwise magic".

edited Mar 28, 2011 at 19:59

answered Mar 28, 2011 at 19:29

Grzegorz Szpetkowski

38.2k6 gold badges94 silver badges140 bronze badges

Comments

Nadeem Douba · Accepted Answer · 2024-06-17 13:42:03Z

I've created a series of bitwise operator functions using pgplsql to accommodate for this exact requirement. Here's a sample of xor:

create or replace function numeric_xor(leftarg numeric(1000), rightarg numeric(1000)) returns numeric as
$$
declare 
    width int = 32;
    modulo bigint := 2 ^ width;
    b1 bigint := 0;
    b2 bigint := 0;
    r numeric(1000) := 0;
    i integer := 0;
begin
    while leftarg != 0 or rightarg != 0 loop
            b1 := leftarg % modulo;
            b2 := rightarg % modulo;
            r := r + ((b1 # b2)::numeric << (width * i));
            leftarg = leftarg >> width;
            rightarg = rightarg >> width;
            i := i + 1;
        end loop;
    return r;
end;
$$ language plpgsql;

create operator # (
    function=numeric_xor,
    leftarg=numeric,
    rightarg=numeric
    );

do
$$
    begin
        assert 129401205912050919051920510251920501925::numeric # 12591205901209501209490409120949109249012040124::numeric = 12591206029860257193562230384726988007386197273;
    end;
$$;

The other operators can be found here: https://gist.github.com/allfro/d93cbc6980f38cf309555eff77381ced

Collectives™ on Stack Overflow

XOR between numeric in PostgreSQL

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related