PostgreSQL – How to List All Schemas

postgresqlpostgresql-9.1schema

When using PostgreSQL v9.1, how do I list all of the schemas using SQL?

I was expecting something along the lines of:

SELECT something FROM pg_blah;

Best Answer

To lists all schemas, use the (ANSI) standard INFORMATION_SCHEMA

select schema_name
from information_schema.schemata;

More details in the manual

alternatively:

select nspname
from pg_catalog.pg_namespace;

More details about pg_catalog in the manual

Related Solutions

PostgreSQL schemas and search_path

The citation from the SET related paragraph of the ALTER DATABASE documentation section is "Whenever a new session is subsequently started in that database, the specified value becomes the session default value". So the changes will take effect for new sessions only. You just need to reconnect.

Postgresql – How to list all views in SQL in PostgreSQL

From the documentation:

 select table_name from INFORMATION_SCHEMA.views;

If you don't want the system views is your result, try this:

 select table_name from INFORMATION_SCHEMA.views WHERE table_schema = ANY (current_schemas(false))

Best Answer

Related Solutions

PostgreSQL schemas and search_path

Postgresql – How to list all views in SQL in PostgreSQL

Related Question