Re: Division by zero - Mailing list pgsql-general

From Gurjeet Singh
Subject Re: Division by zero
Date
Msg-id 65937bea0906031142s53a30472v2dbb39bc9f105be2@mail.gmail.com
Whole thread Raw
In response to Division by zero  (Oliver Kohll - Mailing Lists <oliver.lists@gtwm.co.uk>)
Responses Re: Division by zero
List pgsql-general
On Wed, Jun 3, 2009 at 9:18 PM, Oliver Kohll - Mailing Lists <oliver.lists@gtwm.co.uk> wrote:
Hello,

We have a system that allows users to create views containing calculations but divisions by zero are commonly a problem.

An simple example calculation in SQL would be

SELECT cost / pack_size AS unit_cost from products;

Either variable could be null or zero.

I don't think there's a way of returning null or infinity for divisions by zero, rather than causing an error but I'd just like to check - and put in a vote for that functionality!

If not, I will have to get the system to check for any numeric fields in user input calculations and rewrite them similar to

CASE WHEN cost IS NULL THEN null
WHEN pack_size IS NULL THEN null
WHEN cost = 0 THEN null
WHEN pack_size = 0 THEN null
ELSE cost / pack_size
AS unit_cost

I don't want to write new functions, I'd rather keep it in plain SQL.


Putting that in a function is definitely going to be expensive..

You need to take care of only one case here: denominator == 0; rest of the cases will be handled sanely by the database.

CASE WHEN packet_size = 0 THEN null ELSE cost/packet_size END as unit_cost

Best regards,

--
Lets call it Postgres

EnterpriseDB      http://www.enterprisedb.com

gurjeet[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo }.com
Mail sent from my BlackLaptop device

pgsql-general by date:

Previous
From: pribram pribram
Date:
Subject: Re: [GENERAL] lc_messages 8.3.7
Next
From: Geoffrey
Date:
Subject: Re: warm standby with WAL shipping