Re: Calculating percentages in Postgresql

Peter Nixonn <listuser(at)peternixon(dot)net> writes:

> Hi Guys
>
>
> Has anyone written a postgres function to calculate percentages without
> giving "ERROR: division by zero" when fed zeros?
>

One simple way to hack around this is wrap the problematic operand in
'nullif' and let it compute a null result where div by zero is
avoided.

jerry(at)jerry#
= \pset null 'Oops!'
Null display is "Oops!".
jerry(at)jerry#
= select 100 / nullif(num, 0) from generate_series(0,3) foo (num);
?column?
----------
Oops!
100
50
33
(4 rows)

jerry(at)jerry#
=

> --
>
> Peter Nixon
> http://www.peternixon.net/
> PGP Key: http://www.peternixon.net/public.asc
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

--
-------------------------------------------------------------------------------
Jerry Sievers 305 854-3001 (home) Production Database Administrator
305 321-1144 (mobil WWW E-Commerce Consultant