Re: Division by zero.

CASE WHEN expr THEN expr ELSE expr END

as in

select CASE WHEN baz == 0 THEN null ELSE bar/baz END;

See Postgresql documentation, Chapter 5, SQL functions.






Warren Vanichuk <pyber@street-light.com>@postgresql.org on 11/27/2000
07:01:06 PM

Sent by:  pgsql-general-owner@postgresql.org


To:   pgsql-general@postgresql.org
cc:

Subject:  [GENERAL] Division by zero.



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