List user created schemas in Redshift

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

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

0
There are no comments. Click here to write the first comment.