Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 6E6D7475ED4 for ; Wed, 16 Oct 2002 23:10:30 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id ACD4A475E71 for ; Wed, 16 Oct 2002 23:10:29 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g9H3AHhR029431; Wed, 16 Oct 2002 23:10:17 -0400 (EDT) To: Stephan Szabo Cc: Albrecht Berger , pgsql Subject: Re: replace null with 0 in subselect ? In-reply-to: <20021016080309.A94982-100000@megazone23.bigpanda.com> References: <20021016080309.A94982-100000@megazone23.bigpanda.com> Comments: In-reply-to Stephan Szabo message dated "Wed, 16 Oct 2002 08:03:43 -0700" Date: Wed, 16 Oct 2002 23:10:17 -0400 Message-ID: <29430.1034824217@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/189 X-Sequence-Number: 9906 Stephan Szabo 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