List All Schemas in Your PostgreSQL Database

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

How to List All Schemas in a PostgreSQL Database

information_schema.schemata is the SQL-standard view for listing schemas, and it returns one row per schema in the current database that the connected role owns or holds a privilege on. Behind it sits pg_namespace, the system catalog that stores every schema with its owner and access list. The two answer slightly different questions, and picking the wrong one is how a schema audit misses the schemas the connected role cannot access.

Purpose and Overview

A schema is a namespace. Each one holds its own tables, views, functions, types and sequences, and two schemas can contain objects with the same name without conflict. A fresh database has the public schema plus the system schemas. A production database often has dozens more: one per application module, one per tenant, one per migration tool, and a few left behind by projects nobody remembers.

A schema list is the first step of several routine jobs: checking who owns what before a role is dropped, finding empty or abandoned schemas, confirming that a tenant schema was created, and scoping a pg_dump -n run. The queries below cover the quick list, a detailed audit that adds owner, table count, comment and access, and a filtered list that leaves the system schemas out.

Sample Code

Simple list using information_schema:

1SELECT schema_name, schema_owner
2FROM information_schema.schemata
3ORDER BY schema_name;

Detailed audit using pg_namespace:

 1SELECT
 2    n.nspname                              AS schema_name,
 3    pg_catalog.pg_get_userbyid(n.nspowner) AS owner,
 4    count(c.oid) FILTER (WHERE c.relkind IN ('r', 'p'))
 5                                           AS tables,
 6    has_schema_privilege(n.nspname, 'USAGE')  AS can_use,
 7    has_schema_privilege(n.nspname, 'CREATE') AS can_create,
 8    obj_description(n.oid, 'pg_namespace') AS comment
 9FROM pg_catalog.pg_namespace n
10LEFT JOIN pg_catalog.pg_class c ON c.relnamespace = n.oid
11WHERE n.nspname !~ '^pg_'
12  AND n.nspname <> 'information_schema'
13GROUP BY n.oid, n.nspname, n.nspowner
14ORDER BY n.nspname;

List only user-created schemas from the standard view:

1SELECT schema_name
2FROM information_schema.schemata
3WHERE schema_name !~ '^pg_'
4  AND schema_name <> 'information_schema'
5ORDER BY schema_name;

Tables inside one schema:

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

Notes: The second query returns every schema in pg_namespace that is not a system schema, whether or not the current role has any privilege on it. Replace myapp in the last query with a real schema name. In psql, \dn gives a quick version of the first query, \dn+ adds privileges and descriptions, and \dnS includes the system schemas.

Code Breakdown

The first two queries read different sources and return different row sets, so the breakdown follows the data rather than the statements.

information_schema.schemata

The view reports catalog_name (always the current database), schema_name, and schema_owner. It also has default_character_set_catalog, default_character_set_schema, default_character_set_name and sql_path, all of which apply to a feature not available in PostgreSQL, so they carry no useful data. Because the view filters by privilege, a role that has no rights on a schema does not see it here.

pg_namespace and pg_get_userbyid

pg_namespace has four columns: oid, nspname, nspowner and nspacl. The owner is stored as an OID that references pg_authid, so pg_get_userbyid(n.nspowner) converts it to a role name. The catalog is not filtered by privilege, which makes it the right source for an audit.

Counting tables with pg_class

pg_class stores relations, and relnamespace ties each one to a row in pg_namespace. The LEFT JOIN keeps schemas that have no relations at all, so an empty schema shows a table count of zero instead of disappearing. The FILTER clause counts only relkind values r (ordinary table) and p (partitioned table); indexes, sequences and TOAST tables share the same catalog and would otherwise inflate the number.

Privilege checks

has_schema_privilege(schema, privilege) takes USAGE or CREATE and answers for the current role. When can_use is false, the schema exists but the role cannot look up objects in it. The two-argument form checks the current user; a three-argument form accepts a role name or OID first.

The system-schema filter

Names that begin with pg_ are reserved for system purposes and cannot be created by users, so the regular expression '^pg_' removes pg_catalog, the per-session temporary schemas and any other system namespace. information_schema does not begin with pg_, so it is excluded by name. A LIKE 'pg_%' test is a common mistake here: in a LIKE pattern the underscore matches any single character, so it would also hide a user schema called pgstats or pgx. The regular expression or an escaped LIKE 'pg\_%' avoids that.

Key Schema Concepts

A few facts about how schemas behave explain what the queries return.

The public schema

Every new database contains a schema named public. Objects created without a schema name land in the first valid schema on the search path, which by default is public for most roles.

pg_catalog and temporary schemas

Each database contains pg_catalog, which holds the system tables and all built-in data types, functions and operators. The current session's temporary-table schema is pg_temp_nnn, and it is always searched for relation and data type names if it exists. Because both start with pg_, the user-schema filters above skip them.

search_path

search_path is the comma-separated list that decides which schema supplies an unqualified name. The default is "$user", public: PostgreSQL first looks for a schema with the same name as the current role, then public. Entries that name a nonexistent schema, or one the role has no USAGE on, are silently ignored. That silent behavior is a common cause of "relation does not exist" errors after a schema is renamed.

1SHOW search_path;
2SET search_path TO myapp, public;

USAGE and CREATE

USAGE on a schema allows a role to look up objects inside it, provided the objects' own privileges are also met. CREATE allows creating objects in the schema. Without USAGE, the object names can still be seen by querying the system catalogs, which is why pg_namespace shows schemas that information_schema.schemata hides.

Practical Applications

Schema listings feed a handful of recurring tasks.

Tenant and module inventory

On a database that uses one schema per tenant, the detailed query shows each schema, its owning role and its table count. A tenant schema with zero tables is usually a failed provisioning run. Schemas whose table counts differ from the rest are the ones to compare against the template before a migration.

Pre-drop and ownership review

Before dropping a role, list the schemas it owns. A schema can only be dropped by its owner or a superuser, and the owner can drop it even when other roles own objects inside. The owner column shows which schemas need ALTER SCHEMA ... OWNER TO first.

Scoping a backup

pg_dump -n selects one schema and everything it contains, and -N excludes schemas by pattern. Listing the user schemas first gives the exact names to pass. A schema-level dump does not pull in other objects the schema depends on, so it cannot be assumed to restore alone into an empty database.

Access review

The can_use and can_create columns show what the connected role can do. Run the query as an application role to confirm it reaches the schemas it is supposed to, and as a read-only role to confirm it cannot create objects anywhere unexpected.

Version Compatibility

Every view, catalog column and function in the queries above appears in the current manual under the names used here, so no version-specific rewrite is needed for them.

The notable version change concerns the public schema. From PostgreSQL 15 the default configuration supports the pattern of user-private schemas, in which ordinary users cannot create objects in public. On earlier releases, and on databases upgraded from them, that protection has to be added with REVOKE CREATE ON SCHEMA public FROM PUBLIC. The can_create column in the detailed query shows the current state for any role you test.

Best Practices

  • Audit from pg_namespace, report from information_schema — the catalog shows every schema, while the view shows only what the role can access, so use the one that matches the question.
  • Never filter system schemas with an unescaped LIKE — LIKE 'pg_%' also matches user schemas such as pgstats; use ~ '^pg_' or LIKE 'pg\_%'.
  • Comment every schema — COMMENT ON SCHEMA myapp IS 'Billing tables' makes the description column useful; obj_description() returns null when no comment exists.
  • Create schemas with an owner — CREATE SCHEMA myapp AUTHORIZATION app_owner sets ownership in one step.
  • Treat DROP SCHEMA ... CASCADE as irreversible — it removes every contained object and may also remove objects in other schemas that depend on them; list the contents first.
  • Check the search_path of application roles — a role whose path names a schema it cannot use silently skips it, so verify with has_schema_privilege().

References

Posts in this series