Division by zero

Hello,

Divide by zero errors have come up a couple of times on this list
(once raised by me). I wonder if I could propose a feature for
discussion. Could the result of a division by zero be treated as
infinity or null, rather than raising an error? Floating point types
already have the concept of infinity.

I'd have thought that there's no reason why a /0 in one row
necessarily has to be fatal for the whole view. In many cases, you can
imagine that returning infinity makes more sense. Strictly, I suppose,
1/0 should return infinity, 0/0 null and -1/0 negative infinity.
Alternatively, all could return NaN. At least there could be a
configuration option to turn on this behaviour.

The concern stems from the fact that when a divide by zero occurs in a
view, no rows at all are returned, just the error message. This makes
it very difficult to work out where the problem value is, compared to
other tools like spreadsheets, which return a cell error. A view can
be very fragile. Further, the Postgres error doesn't give any details
of the field and of course can't point to the row, it just says
ERROR: division by zero

There may well be good reasons for not treating this. I've come across
comments such as 'I think everybody would agree that this would be a
bad thing to do!' but remain to be convinced.

I know you can use CASE and NULLIF but if you have complex
calculations, that makes them a lot less readable.

Regards
Oliver Kohll

oliver(at)gtwm(dot)co(dot)uk / 0845 456 1810 / 07814 828608
www.gtwm.co.uk - company
www.gtportalbase.com - product