[GENERAL] 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
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
Re: [GENERAL] correlated query as a column and where clause
On Fri, Apr 15, 2011 at 11:22 AM, salah jubeh 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!
Re: [GENERAL] correlated query as a column and where clause
Hello All, 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_views 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; But, why I can not use the alias of the select statement ( as in the original post) in the where clause. Regards From: Chris Curvey To: salah jubeh Cc: pgsql 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 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!
Re: [GENERAL] correlated query as a column and where clause
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 To: salah jubeh Cc: pgsql 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
Re: [GENERAL] correlated query as a column and where clause
salah jubeh 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
Re: [GENERAL] correlated query as a column and where clause
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 To: salah jubeh Cc: ch...@chriscurvey.com; pgsql Sent: Fri, April 15, 2011 5:49:35 PM Subject: Re: [GENERAL] correlated query as a column and where clause salah jubeh 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
