By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,903 Members | 1,104 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,903 IT Pros & Developers. It's quick & easy.

Divide by zero...

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
When grilled further on (Tue, 30 Sep 2003 09:26:19 -0400),
Mike Leahy <mg*****@fes.uwaterloo.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)

iEYEARECAAYFAj95jecACgkQgy51bQc2FFnYdgCgh3c8tRl8GA sUPCUr0UKFe5M4
0PMAoKMl474yGB6SgU5N7RAT6i/Q/UdN
=Twhx
-----END PGP SIGNATURE-----

Nov 12 '05 #2

P: n/a


Robert Creager wrote:
When grilled further on (Tue, 30 Sep 2003 09:26:19 -0400),
Mike Leahy <mg*****@fes.uwaterloo.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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.