Обсуждение: correlated query as a column and where clause
Hello All,
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_'
Thanks in advance
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_'
Thanks in advance
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
--
Harald Armin Massa www.2ndQuadrant.com
PostgreSQL Training, Services and Support
2ndQuadrant Deutschland GmbH
GF: Harald Armin Massa
Amtsgericht Stuttgart, HRB 736399
On Fri, Apr 15, 2011 at 11:22 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
Hello All,
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_'
I can't answer your question directly, but I would rewrite the query as:
select pg_namespace.nspname, count(*)
from pg_namespace
join pg_tables on pg_namespace.nspname = pg_tables.schemaname
where pg_namespace.nspname not like 'pg_%'
group by pg_namespace.nspname
having count(*) > 10
Thanks in advance
--
Ignoring that little voice in my head since 1966!
Hello All,
The following query give me what I want.
Regards
From: Chris Curvey <chris@chriscurvey.com>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: pgsql <pgsql-general@postgresql.org>
Sent: Fri, April 15, 2011 5:28:39 PM
Subject: Re: [GENERAL] correlated query as a column and where clause
--
Ignoring that little voice in my head since 1966!
The following query give me what I want.
Select nspname, COALESCE(t_count.count,0) as num_of_tables, COALESCE(v_count.count,0) as num_of_viewsBut, why I can not use the alias of the select statement ( as in the original post) in the where clause.
FROM pg_namespace
Left Join (SELECT schemaname, count(*) as count FROM pg_tables group by schemaname) t_count on (t_count.schemaname = nspname)
Left Join (SELECT schemaname, count(*) as count FROM pg_views group by schemaname) v_count on (v_count.schemaname = nspname)
where nspname !~* 'pg_' and (COALESCE(t_count.count,0)+COALESCE(v_count.count,0) <= 2)
order by 1,2;
Regards
From: Chris Curvey <chris@chriscurvey.com>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: pgsql <pgsql-general@postgresql.org>
Sent: Fri, April 15, 2011 5:28:39 PM
Subject: Re: [GENERAL] correlated query as a column and where clause
On Fri, Apr 15, 2011 at 11:22 AM, salah jubeh <s_jubeh@yahoo.com> wrote:
Hello All,
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_'
I can't answer your question directly, but I would rewrite the query as:
select pg_namespace.nspname, count(*)
from pg_namespace
join pg_tables on pg_namespace.nspname = pg_tables.schemaname
where pg_namespace.nspname not like 'pg_%'
group by pg_namespace.nspname
having count(*) > 10
Thanks in advance
--
Ignoring that little voice in my head since 1966!
Hello Harald,
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
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
--
Harald Armin Massa www.2ndQuadrant.com
PostgreSQL Training, Services and Support
2ndQuadrant Deutschland GmbH
GF: Harald Armin Massa
Amtsgericht Stuttgart, HRB 736399
salah jubeh <s_jubeh@yahoo.com> writes: > But, why I can not use the alias of the select statement ( as in the original > post) in the where clause. The select list can only be computed after the where clause has filtered the rows, so such a thing would be circular. regards, tom lane
Hello Tom
Sorry, but I did not get you. I know that the filtering in the where clause and can be pushed up or down in the parsing tree depending on the optimizer. So in this certain case, the result could be computed first and filtered by the where clause later.
Regards
From: Tom Lane <tgl@sss.pgh.pa.us>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: chris@chriscurvey.com; pgsql <pgsql-general@postgresql.org>
Sent: Fri, April 15, 2011 5:49:35 PM
Subject: Re: [GENERAL] correlated query as a column and where clause
salah jubeh <s_jubeh@yahoo.com> writes:
> But, why I can not use the alias of the select statement ( as in the original
> post) in the where clause.
The select list can only be computed after the where clause has filtered
the rows, so such a thing would be circular.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Sorry, but I did not get you. I know that the filtering in the where clause and can be pushed up or down in the parsing tree depending on the optimizer. So in this certain case, the result could be computed first and filtered by the where clause later.
Regards
From: Tom Lane <tgl@sss.pgh.pa.us>
To: salah jubeh <s_jubeh@yahoo.com>
Cc: chris@chriscurvey.com; pgsql <pgsql-general@postgresql.org>
Sent: Fri, April 15, 2011 5:49:35 PM
Subject: Re: [GENERAL] correlated query as a column and where clause
salah jubeh <s_jubeh@yahoo.com> writes:
> But, why I can not use the alias of the select statement ( as in the original
> post) in the where clause.
The select list can only be computed after the where clause has filtered
the rows, so such a thing would be circular.
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general