Received: from sss.pgh.pa.us ([192.204.191.242]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f7OKvGf04684 for ; Fri, 24 Aug 2001 16:57:16 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.11.4/8.11.4) with ESMTP id f7OKuiK08811; Fri, 24 Aug 2001 16:56:44 -0400 (EDT) To: Joseph Shraibman cc: pgsql-sql Subject: Re: WHERE on an alias In-reply-to: <3B8698E7.4080106@selectacast.net> References: <3B8698E7.4080106@selectacast.net> Comments: In-reply-to Joseph Shraibman message dated "Fri, 24 Aug 2001 14:11:51 -0400" Date: Fri, 24 Aug 2001 16:56:44 -0400 Message-ID: <8808.998686604@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200108/308 X-Sequence-Number: 4555 Joseph Shraibman writes: > playpen=# select a, sum(b) as dsum from taba where dsum > 5 group by(a); > ERROR: Attribute 'dsum' not found > Why can we GROUP BY on an alias but not do a WHERE on an alias? Because WHERE is computed before the select's output list is. Strictly speaking you shouldn't be able to GROUP on an alias either (the SQL spec doesn't allow it). We accept that for historical reasons only, ie, our interpretation of GROUP used to be wrong and we didn't want to break applications that relied on the wrong interpretation. Note that writing a GROUP on an alias does *not* mean the alias is only computed once. It saves no computation, only writing out the expression twice. > I have a subselect that > explain shows is being run twice if I have to put it in the WHERE clause. Possibly you could restructure your query into something with a subselect in the FROM clause? regards, tom lane