Greetings.
I am wanting to order a query by a resulting ratio that's determined by the
division of two columns. 99.99% of the time, the columns will only numbers
greater than 0, but occasionally the columns will be 0, resulting in a
divide by zero error.
The behaviour I was expecting (from working with other databases) was that
if a divide by zero occured, the column would merely be NULL in the returned
set of data. Under PostgreSQL it appears to actually abort the transaction,
and return no data.
freehost=> create table foo( bar int4, baz int4 );
CREATE
freehost=> insert into foo values ( 1, 0 );
INSERT 38390 1
freehost=> insert into foo values ( 0 , 1 );
INSERT 38391 1
freehost=> insert into foo values ( 1, 1 );
INSERT 38392 1
freehost=> select ( bar / baz ) as redpill from foo;
ERROR: floating point exception! The last floating point operation either exceeded legal ranges or was a divide by
zero
is the current results. What are the possibilities of this behaviour being
changed in future revisions so that the results returned would be :
redpill
-------
NULL
0
1
?
Is there a workaround for this that I'm not seeing?
Any help would be appriecated.. :)
Sincerely, Warren