Re: Calculating percentages in Postgresql

Peter Nixonn <listuser@peternixon.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@jerry#
= \pset null 'Oops!'
Null display is "Oops!".
jerry@jerry#
= select 100 / nullif(num, 0) from generate_series(0,3) foo (num);
 ?column?
----------
    Oops!
      100
       50
       33
(4 rows)

jerry@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@postgresql.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