Re: About PostgreSQL's limit on arithmetic operations

Signed integers use a number system known as "two's compliment" and in 
order to
make room for negative numbers the most significant bit is used to 
indicate a negative
value and the compliment of the rest of the bits minus 1 {there is no 
such number as -0}
evaluate to the negative numbers. This method is used because it makes 
addition and
subtraction simpler for computers to perform on integers.

I am not sure how to declare an unsigned bigint, but if you can it would 
produce
the expected result.

Only Oracle developers could tell you why they would generate an invalid 
result when
doing math using 64 bit signed integers {9223372036854775808 is not a 
valid value
for a 64bit signed integer.} Just because Oracle gives you the number 
you want it
in no way makes the result correct. Since the result is outside the 
scope of a 64 bit
signed integer an overflow error could be a valid result, or the value 
postgresql
returns could also be a valid result. If you are multiplying by 512 in 
order to perform
a binary right shift of 9 bits on the integer you don't want scope 
validation, because if
were enforced a right shift would not work, and the result would produce 
an error.
Please read the documentation on data types, it details the scope 
{range} for all data
types. If you use the proper data type for your data set you can reduce 
the amount of
storage required for small numbers and be fairly certain that the math 
will work as
expected using large numbers.



Shridhar Daithankar wrote:

>On Wednesday 29 Sep 2004 2:25 pm, Devrim GUNDUZ wrote:
>  
>
>>>template1=# SELECT 512*18014398509481984::numeric(20) AS result;
>>>      result
>>>---------------------
>>>9223372036854775808
>>>(1 row)
>>>      
>>>
>>Ok, I got the same result in 7.4.5... But... Why do we have to cast it
>>into numeric? The results from other databases shows that they can perform
>>it without casting...
>>    
>>
>
>Probably because the normal integer is 4 bytes long and bigint is 8 bytes 
>long. The value above is exactly 2^63 at which a 8 bytes long signed bigint 
>should flip sign/overflow. I am still puzzled with correct value and negative 
>sign..
>
>For arbitrary precision integer, you have to use numeric. It is not same as 
>oracle. 
>
>Furthermore if your number fit in range, then numbers like precision(4,0) in 
>oracle to smallint in postgresql would buy you huge speed 
>improvement(compared to postgresql numeric I mean)
>
>Please correct me if I am wrong..
> 
> Shridhar
>  
>

-- 
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.