pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedreplace null with 0 in subselect ?
4+ messages / 4 participants
[nested] [flat]
* replace null with 0 in subselect ?
@ 2002-10-16 10:07 Albrecht Berger <berger1517@gmx.ch>
0 siblings, 2 replies; 4+ messages in thread
From: Albrecht Berger @ 2002-10-16 10:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: replace null with 0 in subselect ?
@ 2002-10-16 12:16 Ian Barwick <barwick@gmx.net>
parent: Albrecht Berger <berger1517@gmx.ch>
1 sibling, 0 replies; 4+ messages in thread
From: Ian Barwick @ 2002-10-16 12:16 UTC (permalink / raw)
To: Albrecht Berger <berger1517@gmx.ch>; pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: replace null with 0 in subselect ?
@ 2002-10-16 15:03 Stephan Szabo <sszabo@megazone23.bigpanda.com>
parent: Albrecht Berger <berger1517@gmx.ch>
1 sibling, 1 reply; 4+ messages in thread
From: Stephan Szabo @ 2002-10-16 15:03 UTC (permalink / raw)
To: Albrecht Berger <berger1517@gmx.ch>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: replace null with 0 in subselect ?
@ 2002-10-17 03:10 Tom Lane <tgl@sss.pgh.pa.us>
parent: Stephan Szabo <sszabo@megazone23.bigpanda.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2002-10-17 03:10 UTC (permalink / raw)
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>; +Cc: Albrecht Berger <berger1517@gmx.ch>; pgsql-sql
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 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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-10-17 03:10 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-16 10:07 replace null with 0 in subselect ? Albrecht Berger <berger1517@gmx.ch>
2002-10-16 12:16 ` Ian Barwick <barwick@gmx.net>
2002-10-16 15:03 ` Stephan Szabo <sszabo@megazone23.bigpanda.com>
2002-10-17 03:10 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox