List All Schemas in Your PostgreSQL Database

calendar Apr 13, 2026 / · 3 min read · postgresql administration database information_schema pg_namespace  ·
Share on: linkedin copy

How to List All Schemas in a PostgreSQL Database

A PostgreSQL database can contain multiple schemas. Schemas act as namespaces that group tables, views, functions, and other objects. In a fresh database you will find public and a set of system schemas. In a larger application database you may find dozens of application schemas, one per tenant, or schemas that separate different parts of the codebase.

Knowing which schemas exist and who owns them is a basic but essential administrative task. These queries give you that information quickly.

Sample Code

Simple list using information_schema:

1SELECT schema_name
2FROM information_schema.schemata;

Detailed list with owner and type using pg_namespace:

 1SELECT
 2    n.nspname                         AS schema_name,
 3    pg_catalog.pg_get_userbyid(n.nspowner) AS owner,
 4    CASE
 5        WHEN n.nspname LIKE 'pg_%'    THEN 'system'
 6        WHEN n.nspname = 'information_schema' THEN 'system'
 7        ELSE 'user'
 8    END                               AS schema_type,
 9    obj_description(n.oid, 'pg_namespace') AS description
10FROM
11    pg_catalog.pg_namespace n
12ORDER BY
13    schema_type, schema_name;

List only user-created schemas:

1SELECT schema_name
2FROM information_schema.schemata
3WHERE schema_name NOT LIKE 'pg_%'
4  AND schema_name <> 'information_schema'
5ORDER BY schema_name;

Notes: Works on all supported PostgreSQL versions. information_schema.schemata is the SQL-standard view and the simplest option. pg_namespace is the underlying system catalog and provides more detail including the owner OID and object description.

Code Breakdown

  • information_schema.schemata — A SQL-standard view that lists all schemas visible to the current user. The key column is schema_name. It also includes schema_owner and default_character_set_name (always null in PostgreSQL).
  • pg_catalog.pg_namespace — The raw system catalog behind information_schema.schemata. Contains the schema name (nspname), owner OID (nspowner), and access control list (nspacl).
  • pg_catalog.pg_get_userbyid(n.nspowner) — Converts the owner OID to a readable role name.
  • obj_description(n.oid, 'pg_namespace') — Returns the comment set on the schema with COMMENT ON SCHEMA, if any. Returns null when no comment exists.
  • n.nspname LIKE 'pg_%' — Matches PostgreSQL internal schemas (pg_catalog, pg_toast, pg_temp_*). These are created and managed by PostgreSQL itself.
  • schema_name <> 'information_schema' — Excludes the SQL-standard information schema, which is also a system schema despite not starting with pg_.
  • schema_type — A derived column that labels schemas as system or user for easy filtering and sorting.

Key Points

  • The public schema is created by default and is where most user objects land unless explicitly placed elsewhere.
  • System schemas (pg_catalog, pg_toast, information_schema) are always present and should not be modified.
  • Temporary schemas (pg_temp_N) appear while a session has active temporary tables. They are automatically dropped when the session ends.

Insights and Explanations

Why schemas matter:

Schemas are the primary way to organize objects within a single PostgreSQL database. Common patterns include:

  • Application schemas: One schema per application module (e.g., billing, auth, reporting).
  • Tenant schemas: One schema per customer in a multi-tenant application. Each tenant's tables are isolated by schema.
  • Versioned schemas: Some migration tools create schemas like v1, v2 to manage API versioning at the database level.

search_path and schema visibility:

PostgreSQL resolves unqualified object names using search_path. By default, search_path is "$user", public. This means PostgreSQL first looks for a schema matching your username, then falls back to public. Objects in other schemas must be referenced with their full schema-qualified name: schema_name.table_name.

Check the current search path:

1SHOW search_path;

Set the search path for a session:

1SET search_path TO myapp, public;

Checking what is in a schema:

Once you know which schemas exist, you can list tables within a specific schema:

1SELECT tablename
2FROM pg_tables
3WHERE schemaname = 'myapp'
4ORDER BY tablename;

Additional Considerations

  • Permissions: Any user can query information_schema.schemata and pg_namespace, but they will only see schemas they have USAGE privilege on. Superusers see all schemas.
  • Creating schemas: Use CREATE SCHEMA schema_name AUTHORIZATION role_name to create a new schema and assign ownership in one step.
  • Dropping schemas: DROP SCHEMA schema_name CASCADE removes a schema and all objects within it. This is irreversible — use with care.

References

Posts in this series