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(at)street-light(dot)com>@postgresql.org on 11/27/2000
07:01:06 PM

Sent by: pgsql-general-owner(at)postgresql(dot)org

To: pgsql-general(at)postgresql(dot)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