Divide by zero...

[Mike Leahy]

All...

I have a query that calculates various using variables from a survey
database. As with any survey, there are many instantces of null values. I'm
wondering if there is any way to escape the error caused by dividing by zero
or null values. The specific message i get is:

ERROR: floating point exception! The last floating point operation either
exceeded legal ranges or was a divide by zero

Is there a simple trick that won't make my queries excessively complex?

Many thanks.

Mike

----------------------------------------
This mail sent through www.mywaterloo.ca

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

[Robert Creager]

When grilled further on (Tue, 30 Sep 2003 09:26:19 -0400),
Mike Leahy <mg*****@fes.uw aterloo.ca> confessed:

I have a query that calculates various using variables from a survey
database. As with any survey, there are many instantces of null values. I'm
wondering if there is any way to escape the error caused by dividing by zero
or null values. The specific message i get is:

ERROR: floating point exception! The last floating point operation either
exceeded legal ranges or was a divide by zero

Is there a simple trick that won't make my queries excessively complex?

I believe CASE and COALESCE will solve your problem. Something like this:

SELECT CASE COALESCE( denom, 0.0 )
WHEN 0.0 THEN 0.0
ELSE COALESCE( num, 0.0 ) / denom
END
FROM some_table;

Cheers,
Rob

--
07:48:16 up 60 days, 19 min, 4 users, load average: 2.32, 2.78, 2.97

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iEYEARECAAYFAj9 5jecACgkQgy51bQ c2FFnYdgCgh3c8t Rl8GAsUPCUr0UKF e5M4
0PMAoKMl474yGB6 SgU5N7RAT6i/Q/UdN
=Twhx
-----END PGP SIGNATURE-----

[Jan Wieck]

Robert Creager wrote:

When grilled further on (Tue, 30 Sep 2003 09:26:19 -0400),
Mike Leahy <mg*****@fes.uw aterloo.ca> confessed:

I have a query that calculates various using variables from a survey
database. As with any survey, there are many instantces of null values. I'm
wondering if there is any way to escape the error caused by dividing by zero
or null values. The specific message i get is:

ERROR: floating point exception! The last floating point operation either
exceeded legal ranges or was a divide by zero

Is there a simple trick that won't make my queries excessively complex?

I believe CASE and COALESCE will solve your problem. Something like this:

SELECT CASE COALESCE( denom, 0.0 )
WHEN 0.0 THEN 0.0
ELSE COALESCE( num, 0.0 ) / denom
END
FROM some_table;

Definitely not. The result of a division by zero is undefined, and that
has a good reason. You cannot substitute it with zero or any other
explicit value without rendering your whole computation absurd. Look at
this simple example:

Let 2a = b | * 2
4a = 2b | + 10a
14a = 2b + 10a | - 7b
14a - 7b = 10a - 5b | ()
7 (2a - b) = 5 (2a - b) | / (2a - b)
7 = 5

Everything is fine, just that the division by (2a - b) is not allowed
because 2a = b and thus (2a - b) = 0. This demonstrates well that
division by zero only leads to nonsense, and nothing else. So please
change the 0.0 case to return NULL instead.
Jan

--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html