Re: replace null with 0 in subselect ?

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> Coalesce((select max(pos)+1 from tab2), 0)
> should work.

Small comment: it's probably noticeably faster to do
(select Coalesce(max(pos), 0) +1 from tab2)

I think that the former will result in two evaluations of the sub-select
in the typical case, because COALESCE(foo, bar) is only a macro for
CASE WHEN foo IS NOT NULL THEN foo ELSE bar END
and that computes foo twice when foo isn't null.

My version isn't perfectly efficient either, because it will run two
copies of the MAX() calculation inside the sub-select; but that's still
better than two sub-selects.

Sometime we should reimplement COALESCE as a primitive instead of a
macro for a CASE expression.

regards, tom lane