List all schemas in PostgreSQL database

Query below lists all schemas in PostgreSQL database. Schemas include default pg_* , information_schema and temporary schemas.

If you want to list user only schemas use this script.

Query

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
order by table_schema;

Columns

• table_schema - schema name
• schema_id - schema id, unique within the database
• owner - principal that owns this schema

Rows

• One row represents one schema in a database
• Scope of rows: all schemas in a database, including default ones
• Ordered by schema name

Sample results

Here is a view of database schemas in pgAdmin (marked in blue - user schemas, marked in red - system schemas):

List user created schemas in PostgreSQL database

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

Query

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;

Columns

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

Rows

• 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)