Thread: How to list all schema names inside a PostgreSQL database through SQL

How to list all schema names inside a PostgreSQL database through SQL

From
Xiaobo Gu
Date:
Hi,

How can I list  all schema names inside a PostgreSQL database through
SQL, especially thoese without any objects created inside it.


Regards,

Xiaobo Gu


Re: How to list all schema names inside a PostgreSQL database through SQL

From
Виктор Егоров
Date:
2012/11/15 Xiaobo Gu <guxiaobo1982@gmail.com>:
> 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


Re: How to list all schema names inside a PostgreSQL database through SQL

From
Achilleas Mantzios
Date:
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


Re: How to list all schema names inside a PostgreSQL database through SQL

From
sk baji
Date:
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 <achill@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: How to list all schema names inside a PostgreSQL database through SQL

From
Ondrej Ivanič
Date:
Hi,

On 15 November 2012 23:31, Xiaobo Gu <guxiaobo1982@gmail.com> 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-hidden        display 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)


Re: How to list all schema names inside a PostgreSQL database through SQL

From
Xiaobo Gu
Date:
Thanks a lot.

On Fri, Nov 16, 2012 at 5:33 AM, Ondrej Ivanič <ondrej.ivanic@gmail.com> wrote:
> Hi,
>
> On 15 November 2012 23:31, Xiaobo Gu <guxiaobo1982@gmail.com> 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-hidden        display 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)


Re: How to list all schema names inside a PostgreSQL database through SQL

From
Xiaobo Gu
Date:
Thanks a lot.

On Fri, Nov 16, 2012 at 5:33 AM, Ondrej Ivanič <ondrej.ivanic@gmail.com> wrote:
> Hi,
>
> On 15 November 2012 23:31, Xiaobo Gu <guxiaobo1982@gmail.com> 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-hidden        display 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)