Re: [GENERAL] field alias in where condition

[Tom Lane]

=?ISO-8859-1?Q?Havasv=F6lgyi_Ott=F3?= <[EMAIL PROTECTED]> writes:
> select substring(proname from 1 to 1) as nevresz, count(*) 
> from pg_proc 
> where nevresz = 'a' 
> order by nevresz 
> group by nevresz;

> What is the problem? I cannot use column alias in where condition?

Exactly.  Per the SQL spec, the WHERE condition is evaluated before the
SELECT list, so it makes no logical sense to do that.  Consider for
example trying to avoid division-by-zero failure like this:

SELECT 1/x AS y WHERE x <> 0;

You'd be really unhappy if 1/x were computed so that it could be made
available in the WHERE condition.

The fact that you're allowed to refer to those aliases in ORDER BY/GROUP
BY is a historical accident stemming from the limited ORDER BY
facilities in ancient versions of the spec.

regards, tom lane

---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

Re: [GENERAL] field alias in where condition

[Richard Huxton]

Havasvölgyi Ottó wrote:

Hi all,

I issued the following queries:


select substring(proname from 1 to 1) as nevresz, count(*) 
from pg_proc 
where nevresz = 'a' 
order by nevresz 
group by nevresz;


select substring(proname from 1 to 1) as nevresz, count(*) 
from pg_proc 
order by nevresz 
group by nevresz;


The first query fails, and says that column 'nevresz' does not exist.
The second is OK.

What is the problem? I cannot use column alias in where condition?


Correct. SQL defines it that way. You could reformulate as a sub-query 
if you wanted:


SELECT nevresz,count(*)
FROM
(
  SELECT substring(proname from 1 to 1) as nevresz FROM pg_proc
) AS dummy
GROUP BY nevresz
ORDER BY nevresz


--
  Richard Huxton
  Archonet Ltd


---(end of broadcast)---
TIP 8: explain analyze is your friend

[GENERAL] field alias in where condition

[Havasvölgyi Ottó]

Hi all,

I issued the following queries:


select substring(proname from 1 to 1) as nevresz, count(*) 
from pg_proc 
where nevresz = 'a' 
order by nevresz 
group by nevresz;

select substring(proname from 1 to 1) as nevresz, count(*) 
from pg_proc 
order by nevresz 
group by nevresz;

The first query fails, and says that column 'nevresz' does not exist.
The second is OK.

What is the problem? I cannot use column alias in where condition?

Thanks,
Otto



---(end of broadcast)---
TIP 8: explain analyze is your friend