Re: null value in queries to default in zero - Mailing list pgsql-general

On Tue, 2002-11-12 at 07:49, Dorward Villaruz wrote:
> select to_char(avg(f1),'FM999999.99') from test where f2 > 7 will yield
> to_char
> --------
>
> (1 row)
>
> is their a way to make the value zero if the return is null?

Sure. The easiest way is:

select    to_char(coalesce(avg(f1), 0), 'FM999999.99')
from      test
where     f2 > 7

You can also do:

select    to_char(case when avg(f1) is not null then avg(f1)
                                                else 0
                                                end, 'FM999999.99')
from      test
where     f2 > 7

coalesce returns the first argument to it that is not null, or null if
they all are. It's actually implemented using the case one, which is
more verbose but more flexible.

Scott