From: Tom Lane <tgl@sss.pgh.pa.us>
To: Stephan Szabo <sszabo@megazone23.bigpanda.com>
Cc: Albrecht Berger <berger1517@gmx.ch>
Cc: pgsql <pgsql-sql@postgresql.org>
Subject: Re: replace null with 0 in subselect ?
Date: Wed, 16 Oct 2002 23:10:17 -0400
Message-ID: <29430.1034824217@sss.pgh.pa.us> (raw)
In-Reply-To: <20021016080309.A94982-100000@megazone23.bigpanda.com>
References: <20021016080309.A94982-100000@megazone23.bigpanda.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, sszabo@megazone23.bigpanda.com, berger1517@gmx.ch
Subject: Re: replace null with 0 in subselect ?
In-Reply-To: <29430.1034824217@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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