PostgreSQL List Schemas: Various Ways to Show All Schemas

In PostgreSQL, a schema serves as a logical container, grouping related database objects together within a designated namespace: tables, views, functions, you name it. By default, every PostgreSQL database contains a schema named public, but you can create as many of those as you wish for better object organization. The role of schemas in PostgreSQL database architecture is quite versatile as they significantly improve numerous aspects of your day-to-day work:

• Modularity and scalability
• Data organization and readability
• Access control and permissions management
• Multi-tenancy and application isolation
• Schema-level backups and restores

As we delve deeper into PostgreSQL database management, it becomes evident that listing schemas is more than just a simple day-to-day task. Sometimes, it can be a strategic must with various advantages. This practice sheds some light on the database structure, providing you with clarity and transparency on the current state of things and allowing new optimization ideas to unfold.

The \dn command is typically used in the SQL Shell (PSQL) command-line tool to list all schemas in a PostgreSQL database. This command provides a list of all schemas available in the current database. Even though widely used, this is not the only way to see the schemas in PostgreSQL databases.

For a more detailed view, you can use an SQL statement instead of a PSQL command and query the information_schema.schemata table:

SELECT schema_name
FROM information_schema.schemata;

Below, you will see screenshots demonstrating the usage of both schema listing methods. The SQL query offers more flexibility for manipulation and integration with other SQL statements, while \dn is a quick and simple way to view schemas directly from the CLI.

As you can see, there are differences in how each command handles the visibility of schemas. System schemas are not displayed by default in certain contexts but are visible in others. Keep that in mind while choosing the schema listing method.

While the command line remains the classical approach to working with databases, using GUI tools can make your life significantly easier by simplifying your daily routine. In this article, we are going to use dbForge Studio for PostgreSQL — an IDE that allows you to create, develop, and execute queries, as well as edit and adjust the code to their requirements in a convenient and user-friendly interface. On top of that, it provides functionality for query formatting and profiling, data reporting and editing, data and schema comparison, building pivot tables, and master-detail relations. You will find the complete list of features on our website.

dbForge Studio offers you the freedom to use SQL queries or its user-friendly graphical interface to achieve the same goals. For instance, to view the list of all schemas in the Bicyclestore database, you can choose either one of these methods and get the same result:

1. In Database Explorer, expand the desired database to see both its system and user schemas.

2. Click New SQL and execute the same query we used in the previous section of this article:

SELECT schema_name
FROM information_schema.schemata;

In the screenshot above, we depicted the results of both methods, which left you with essentially the same outcome: you can view all the schemas in the Bicyclestore database.

We already know how to conjure the list of all schemas in a database. However, understanding the architecture completely requires more than just the schema names. This section will demonstrate how to augment the schema lists with additional details. For example, this SQL query retrieves the names of schemas along with their respective owners:

SELECT
  n.nspname AS schema_name,
  pg_catalog.PG_GET_USERBYID(n.nspowner) AS schema_owner
FROM pg_catalog.pg_namespace n
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema_name;

Depending on your specific needs, you can retrieve even more additional information, such as:

• Number of tables
• Number of views
• Number of functions
• Size
• Dependencies

The following script combines all of these additional bits of information and allows us to paint a more accurate picture:

SELECT
  n.nspname AS schema_name,
  pg_catalog.PG_GET_USERBYID(n.nspowner) AS schema_owner,
  (
    SELECT count(*) FROM pg_catalog.pg_tables WHERE schemaname = n.nspname
  ) AS table_count,
  (
    SELECT count(*) FROM pg_catalog.pg_views WHERE schemaname = n.nspname
  ) AS view_count,
  (
    SELECT count(*) FROM pg_catalog.pg_proc WHERE pronamespace = n.oid
  ) AS function_count
FROM pg_catalog.pg_namespace n
WHERE n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY schema_name;

The result grid will display the schema names, their owners, and the number of tables, views, and functions contained within each schema:

Another piece of valuable additional information we can get about schema is its size. The following query will help us retrieve it:

SELECT
  nspname AS "Schema",
  PG_SIZE_PRETTY(SUM(PG_TOTAL_RELATION_SIZE(C.oid))) AS "Size"
FROM pg_class C
  LEFT JOIN pg_namespace N
    ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY nspname
ORDER BY SUM(PG_TOTAL_RELATION_SIZE(C.oid)) DESC;

On executing the above code, you will see the size of each schema in kilobytes.

The next example showcases a query that can retrieve all the tables from a schema that you specify. In our particular case, this is going to be the production schema from the BicycleStore database:

SELECT tablename
FROM pg_tables
WHERE schemaname = 'production';

The next stop on our today's journey is listing stored functions or procedures in a particular schema. It provides valuable insights into database functionality, facilitates documentation and maintenance tasks, helps analyze dependencies, identifies optimization opportunities, and aids in managing security and access control.

SELECT
  proname
FROM pg_proc p
  JOIN pg_namespace n
    ON p.pronamespace = n.oid
WHERE n.nspname = 'sales';

Let us execute this query to retrieve all the procedures from the sales schema:

PostgreSQL schemas serve as vital organizational structures within databases, providing a logical framework for categorizing and managing data. In this article, we have discussed the importance of listing schemas within PostgreSQL and explored different methods to achieve this goal. Whether you prefer using the command line or a GUI client, solutions like dbForge Studio can revolutionize your schema management routine. Give it a try by downloading a free 30-day trial version today!