Postgres Pro
Downloads
Home   >   mailing lists

Thread: correlated query as a column and where clause

correlated query as a column and where clause

From
salah jubeh
Date:
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: correlated query as a column and where clause

From
Harald Armin Massa
Date:
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: correlated query as a column and where clause

From
Chris Curvey
Date:

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!

Re: correlated query as a column and where clause

From
salah jubeh
Date:
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 <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!

Re: correlated query as a column and where clause

From
salah jubeh
Date:
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

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: correlated query as a column and where clause

From
Tom Lane
Date:
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

Re: correlated query as a column and where clause

From
salah jubeh
Date:
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