Postgres Pro
Downloads
Home   >   mailing lists

Thread: replace null with 0 in subselect ?

replace null with 0 in subselect ?

From
"Albrecht Berger"
Date:
Hello,
I have a statement like this :

INSERT INTO tab1 (c1, c2, c3) VALUES (1,2, SELECT MAX(pos)+1 FROM tab2);

This works fine if the subselect returns a value, but if it returns
null there is a problem. In this case a 0 has to be inserted.
Is there any pg function which solves this problem ?
I know that oracle has a function but I didn't find
something similar in pg.

Thanks
berger

Re: replace null with 0 in subselect ?

From
Ian Barwick
Date:
On Wednesday 16 October 2002 12:07, Albrecht Berger wrote:
> Hello,
> I have a statement like this :
>
> INSERT INTO tab1 (c1, c2, c3) VALUES (1,2, SELECT MAX(pos)+1 FROM tab2);
>
> This works fine if the subselect returns a value, but if it returns
> null there is a problem. In this case a 0 has to be inserted.
> Is there any pg function which solves this problem ?
> I know that oracle has a function but I didn't find
> something similar in pg.

A quick and dirty solution (untested):

INSERT INTO tab1 (c1, c2, c3) 
VALUES (1,2, (SELECT CASE WHEN (SELECT MAX(pos)+1 ) IS NULL THEN 0 ELSE (SELECT MAX(pos)+1 ) END)
)


Ian Barwick
barwick@gmx.net

Re: replace null with 0 in subselect ?

From
Stephan Szabo
Date:
On Wed, 16 Oct 2002, Albrecht Berger wrote:

> Hello,
> I have a statement like this :
>
> INSERT INTO tab1 (c1, c2, c3) VALUES (1,2, SELECT MAX(pos)+1 FROM tab2);

Coalesce is your friend. :)

Coalesce((select max(pos)+1 from tab2), 0)
should work.

Re: replace null with 0 in subselect ?

From
Tom Lane
Date:
Stephan Szabo <sszabo@megazone23.bigpanda.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 forCASE 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