List user created schemas in PostgreSQL database

This query returns list of user created, non-temporary schemas.


select s.nspname as table_schema,
       s.oid as schema_id,
       u.usename as owner
from pg_catalog.pg_namespace s
join pg_catalog.pg_user u on u.usesysid = s.nspowner
where nspname not in ('information_schema', 'pg_catalog', 'public')
      and nspname not like 'pg_toast%'
      and nspname not like 'pg_temp_%'
order by table_schema;


  • schema_name - name of a schema
  • schema_id - id of a schema
  • owner - owner of a schema (default user who made a schema)


  • One row represents one schema
  • Scope of rows: all user created, non temporary schemas
  • Ordered by schema name

Sample results

User created schemas in our pagila repository:

Here is a view of user created schemas in pgAdmin (includin public)

Comments are only visible when the visitor has consented to statistics cookies. To see and add comments please accept statistics cookies.
There are no comments. Click here to write the first comment.