PostgreSQL – List all Schemas

You can query the system catalog table pg_namespace to view all the schemas in Postgres database.

SELECT nspname, oid from pg_catalog.pg_namespace;

nspname is the schema name and oid is the schema id in pg_namespace table.

You can view just the user created schemas by filtering out the system schemas as shown below:

SELECT nspname, oid from pg_catalog.pg_namespace 
where nspname not in ('information_schema', 'pg_catalog', 'public')
and nspname not like 'pg_temp%'
and nspname not like 'pg_toast%';

See also  PostgreSQL - Escape single quote