The following bug has been logged online:

Bug reference: 2846
Logged by: Roman Kononov
Email address: kononov195-pgsql@yahoo.com
PostgreSQL version: 8.2.0 and older
Operating system: linux 2.6.15-27-amd64 ubuntu
Description: inconsistent and confusing handling of underflows, NaNs
and INFs
Details:

Please compare the results of the simple queries.
==============================================
test=# select ('NaN'::float4)::int2;
int2
------
0
(1 row)

test=# select ('NaN'::float4)::int4;
int4
-------------
-2147483648
(1 row)

test=# select ('NaN'::float4)::int8;
ERROR: bigint out of range

test=# select ('nan'::numeric)::int4;
ERROR: cannot convert NaN to integer
==============================================
test=# select abs('INF'::float4);
abs
----------
Infinity
(1 row)

test=# select abs('INF'::float8);
ERROR: type "double precision" value out of range: overflow
==============================================
test=# select -('INF'::float4);
?column?
-----------
-Infinity
(1 row)

test=# select -('INF'::float8);
ERROR: type "double precision" value out of range: overflow
==============================================
test=# select (1e-37::float4)*(1e-22::float4);
?column?
----------
0
(1 row)

test=# select (1e-37::float4)*(1e-2::float4);
ERROR: type "real" value out of range: underflow
==============================================
test=# select (1e-300::float8)*(1e-30::float8);
?column?
----------
0
(1 row)

test=# select (1e-300::float8)*(1e-20::float8);
ERROR: type "double precision" value out of range: underflow
==============================================
test=# select ('INF'::float8-'INF'::float8);
?column?
----------
NaN
(1 row)

test=# select ('INF'::float8+'INF'::float8);
ERROR: type "double precision" value out of range: overflow
==============================================
test=# select ('INF'::float4)::float8;
float8
----------
Infinity
(1 row)

test=# select ('INF'::float8)::float4;
ERROR: type "real" value out of range: overflow
==============================================
test=# select cbrt('INF'::float4);
cbrt
----------
Infinity
(1 row)

test=# select sqrt('INF'::float4);
ERROR: type "double precision" value out of range: overflow
==============================================
test=# select ((-32768::int8)::int2)%(-1::int2);
?column?
----------
0
(1 row)

test=# select ((-2147483648::int8)::int4)%(-1::int4);
ERROR: floating-point exception
DETAIL: An invalid floating-point operation was signaled. This probably
means an out-of-range result or an invalid operation, such
as division by zero.
==============================================
test=# create table tt (ff float8);
CREATE TABLE
test=# insert into tt values (1e308),(1e308),(1e308);
INSERT 0 3
test=# select * from tt;
ff
--------
1e+308
1e+308
1e+308
(3 rows)

test=# select avg(ff) from tt;
avg
----------
Infinity
(1 row)

test=# select stddev(ff) from tt;
stddev
--------
NaN
(1 row)

==============================================
All this things are in float.c

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org