Re: BUG #1762: Integer multiplication error

On Sat, Jul 09, 2005 at 10:25:16AM +0100, Milos Babic wrote:
>
> When I try from psql to select:
>
> select 1024*1024*4500;
>
> it responses with:
> error integer out of range
>
> is it known bug or what is going on?

From "Numeric Constants" in the "SQL Syntax" chapter of the
documentation:

A numeric constant that contains neither a decimal point nor an
exponent is initially presumed to be type integer if its value
fits in type integer (32 bits); otherwise it is presumed to be
type bigint if its value fits in type bigint (64 bits); otherwise
it is taken to be type numeric.

All three constants in the expression meet the criteria for being
an integer, so the expression's result will be an integer as well.
Since the result (4718592000) would overflow 32 bits, PostgreSQL
raises an error (versions prior to 8.0 gave an incorrect result,
as you discovered).

> it also happens when I try to insert 1024*1024*4500 into int8 field in
> database!

The expression has to be evaluated before it can be inserted into
a table, and since it's an integer expression you get the overflow
error. Cast one of the operands to bigint if you want the result
to be a bigint.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/