Re: Division by zero

On Thu, Jun 04, 2009 at 12:12:09AM +0530, Gurjeet Singh wrote:
> On Wed, Jun 3, 2009 at 9:18 PM, Oliver Kohll wrote:
> > SELECT cost / pack_size AS unit_cost from products;
> >
> > Either variable could be null or zero.
> >
> > 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..

If it's an SQL function and marked as IMMUTABLE it should (I believe
anyway) get inlined somewhere along the line and take no more overhead
than writing it out in full.

> 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

Yes; or even shorter:

cost/nullif(packet_size,0) AS unit_cost

--
Sam http://samason.me.uk/