1

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
user545139
  • 935
  • 11
  • 27

2 Answers2

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

Grzegorz Szpetkowski
  • 36,988
  • 6
  • 90
  • 137
1

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

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • 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). – user545139 Mar 28 '11 at 19:53
  • 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. – Andomar Mar 28 '11 at 19:59
  • 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. – user545139 Mar 28 '11 at 20:20
  • @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. – Andomar Mar 28 '11 at 20:25
  • You can use bit strings also. Both support at least 64 bit bit strings. – nate c Mar 28 '11 at 20:40