PostgreSQL offers several ways to list schemas, such as using standard “information_schema”, system catalog table “pg_namespace”, the “\dn” command, etc. These built-in schemas and commands empower us to efficiently access all schemas within a database, ultimately ensuring optimal performance and smooth operation.

This blog will demonstrate various methods along with suitable examples to list the schemas in Postgres. So, let’s start with the “\dn” command.

How to List Schemas Via the “\dn” in Postgres?

The "\dn" command is the most convenient/user-friendly way to get the schemas list in PostgreSQL. It retrieves the list of schemas with their respective owners:

\dn;

The output snippet shows that the “\dn” command retrieves all the available schemas and their owners. You can use the “\dn+” command to get the schema’s list with more details like access privileges and description:

\dn+;

The output snippet shows that the “\dn+” command shows more detailed information for every available schema. To get the information of a specific schema, you need to use the “\dn” or “\dn+” command followed by the schema name, as shown in the following snippet:

\dn exp_schema;

This way, we can get the details for a particular schema with the “\d” command.

How to List Schemas Via the “information_schema” in Postgres?

The INFORMATION_SCHEMA shows the list of all the available schemas. To list all the schemas in a Postgres database, you can query the information_schema as follows:

SELECT schema_name, schema_owner
FROM information_schema.schemata;

The output shows the list of all the available schemas along with their owners.

How to List Schemas Via the “pg_namespace” in Postgres?

To get the list of all the available schemas in a Postgres database, you can use the system catalog table "pg_namespace":

SELECT nspname AS schema_name
FROM pg_catalog.pg_namespace;

The output shows that the “pg_namespace” retrieves the list of available schemas.

That’s all from this blog!

Conclusion

In PostgreSQL, the standard “information_schema”, a system catalog table named “pg_namespace”, and the “\dn” command is used to get the list of available schemas. You can use the “\dn+” command to get the schema’s list with more details like access privileges and description. You can also use the “\dn” or “\dn+” command, followed by the schema name to get the information of a specific schema. This blog post explained different approaches to getting the list of available schemas in PostgreSQL.