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