Re: [GENERAL] How to list all schema names inside a PostgreSQL database through SQL
Thanks a lot. On Fri, Nov 16, 2012 at 5:33 AM, Ondrej Ivanič wrote: > Hi, > > On 15 November 2012 23:31, Xiaobo Gu wrote: >> How can I list all schema names inside a PostgreSQL database through >> SQL, especially thoese without any objects created inside it. > > Use -E psql's option: > -E, --echo-hiddendisplay queries that internal commands generate > > then you get SQL query for each internal command. > > The second option is to use information_schema.schemata view (this is > works across databases) > > > -- > Ondrej Ivanic > (http://www.linkedin.com/in/ondrejivanic) -- 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] How to list all schema names inside a PostgreSQL database through SQL
Thanks a lot. On Fri, Nov 16, 2012 at 5:33 AM, Ondrej Ivanič wrote: > Hi, > > On 15 November 2012 23:31, Xiaobo Gu wrote: >> How can I list all schema names inside a PostgreSQL database through >> SQL, especially thoese without any objects created inside it. > > Use -E psql's option: > -E, --echo-hiddendisplay queries that internal commands generate > > then you get SQL query for each internal command. > > The second option is to use information_schema.schemata view (this is > works across databases) > > > -- > Ondrej Ivanic > (http://www.linkedin.com/in/ondrejivanic) -- 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] How to list all schema names inside a PostgreSQL database through SQL
Hi, On 15 November 2012 23:31, Xiaobo Gu wrote: > How can I list all schema names inside a PostgreSQL database through > SQL, especially thoese without any objects created inside it. Use -E psql's option: -E, --echo-hiddendisplay queries that internal commands generate then you get SQL query for each internal command. The second option is to use information_schema.schemata view (this is works across databases) -- Ondrej Ivanic (http://www.linkedin.com/in/ondrejivanic) -- 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] How to list all schema names inside a PostgreSQL database through SQL
If you are looking for list of empty schema's (No objects in schema), then you can use below query: select nspname from pg_namespace where oid not in (select relnamespace from pg_class) and oid not in (select oid from pg_proc); Regards, Baji Shaik. On Thu, Nov 15, 2012 at 6:13 PM, Achilleas Mantzios < ach...@matrix.gatewaynet.com> wrote: > On Πεμ 15 Ποε 2012 20:31:05 Xiaobo Gu wrote: > > Hi, > > > > How can I list all schema names inside a PostgreSQL database through > > SQL, especially thoese without any objects created inside it. > > > > > > 1st solution : > > select catalog_name,schema_name from information_schema.schemata ; > > 2nd solution : > > select * from pg_namespace ; > > > Regards, > > > > Xiaobo Gu > > > > > > > - > Achilleas Mantzios > IT DEPT > > > -- > 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] How to list all schema names inside a PostgreSQL database through SQL
On Πεμ 15 Îοε 2012 20:31:05 Xiaobo Gu wrote: > Hi, > > How can I list all schema names inside a PostgreSQL database through > SQL, especially thoese without any objects created inside it. > > 1st solution : select catalog_name,schema_name from information_schema.schemata ; 2nd solution : select * from pg_namespace ; > Regards, > > Xiaobo Gu > > > - Achilleas Mantzios IT DEPT -- 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] How to list all schema names inside a PostgreSQL database through SQL
2012/11/15 Xiaobo Gu : > How can I list all schema names inside a PostgreSQL database through > SQL, especially thoese without any objects created inside it. Something like this: select n.nspname, count(o.oid) from pg_namespace n left join pg_class o on n.oid=o.relnamespace group by 1 order by count(o.oid)>0, 1; I prefer to query PostgreSQL catalogs. You can obtain the same information using information_schema queries. -- Victor Y. Yegorov -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
