How to List All Schemas in PostgreSQL Quickly and Easily

To list all schemas in PostgreSQL, use the SQL query SELECT schema_name FROM information_schema.schemata;. This query returns the names of all schemas available in the current database.

Syntax

The basic syntax to list all schemas in PostgreSQL is:

• SELECT schema_name FROM information_schema.schemata; - This selects the schema_name column from the information_schema.schemata view, which holds metadata about all schemas.

This query returns a list of schema names as rows.

sql

SELECT schema_name FROM information_schema.schemata;

Example

This example shows how to run the query to get all schema names in your PostgreSQL database.

sql

SELECT schema_name FROM information_schema.schemata ORDER BY schema_name;

Output

 schema_name 
-------------
 information_schema
 pg_catalog
 public
(3 rows)

Common Pitfalls

Some common mistakes when listing schemas include:

• Using pg_namespace system catalog directly without proper joins, which can be confusing for beginners.
• Expecting schemas to be listed if you are connected to the wrong database, since schemas are database-specific.
• Not having permission to see certain schemas, which means they won't appear in the results.

Always use information_schema.schemata for a simple and reliable list of schemas.

sql

/* Wrong approach: querying pg_namespace without filtering */
SELECT nspname FROM pg_namespace;

/* Right approach: use information_schema.schemata */
SELECT schema_name FROM information_schema.schemata;

Quick Reference

Summary tips for listing schemas in PostgreSQL:

• Use information_schema.schemata for a standard, easy-to-understand list.
• Schemas are specific to the connected database.
• Ordering results by schema_name helps readability.
• Permissions affect which schemas you can see.

Key Takeaways

Use SELECT schema_name FROM information_schema.schemata to list all schemas.

Schemas are specific to the database you are connected to.

Ordering by schema_name makes the list easier to read.

You need proper permissions to see all schemas.

Avoid querying system catalogs directly unless you understand their structure.