Danke ! . My concern is why I get error undefiend attribute if I used the alias in the where clause
Regards
From: Harald Armin Massa <harald@2ndQuadrant.com> To: salah jubeh <s_jubeh@yahoo.com> Cc: pgsql <pgsql-general@postgresql.org> Sent: Fri, April 15, 2011 5:26:45 PM Subject: Re: [GENERAL] correlated query as a column and where clause
I am wondering, why I can not add the following ' A > 10' in the where clause i.e. 'where nspname !~* 'pg_' and A > 10'
Select nspname, (SELECT count(*) as count FROM pg_tables where schemaname = nspname) as A FROM pg_namespace where nspname !~* 'pg_'
what you are looking for is the having clause
Select nspname, count(1)
FROM pg_tables
where nspname !~* 'pg_'
group by nspname
having count(1) >10
best wishes
Harald
-- Harald Armin Massa www.2ndQuadrant.com PostgreSQL Training, Services and Support
2ndQuadrant Deutschland GmbH GF: Harald Armin Massa Amtsgericht Stuttgart, HRB 736399