PostgreSQL List Schemas: Various Ways to Show All Schemas

PostgreSQL is one of many popular relational databases, known for its reliability, extensibility, and robust feature set, making it a preferred choice for many users. A clear understanding of the PostgreSQL schema provides insight into the database architecture. Therefore, the significance of proficient schema-management skills cannot be overestimated.

In this article, we will discuss a fundamental operation that benefits database managers: schema listing. While there are several ways to list all schemas in PostgreSQL, we'll only explore the most useful ones in detail.

What is a PostgreSQL schema?

In PostgreSQL, a schema serves as a logical container, grouping related database objects together within a designated namespace: tables, views, and functions. By default, a 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, including:

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

Why show the PostgreSQL schemas list?

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 necessity 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.

Show schemas in PostgreSQL using the command line

The PostgreSQL show schemas command is a quick way to see which schemas are available. In practice, the command to show schemas in PostgreSQL is: \dn. This is typically used in the SQL Shell (PSQL) command-line tool to list all schemas in a PostgreSQL database. Even though widely used, this is not the only way to see the schemas in PostgreSQL databases.

PostgreSQL show schemas command

If you need more than just a quick list, PostgreSQL also lets you query the system catalog directly. The information_schema.schemata view provides a detailed list of all schemas in the current database.

SELECT schema_name
FROM information_schema.schemata;

The information_schema Postgres catalog is part of the SQL standard and offers a consistent way to access metadata about database objects. Within it, the schemata view specifically lists every schema in the database, including both system (such as pg_catalog and information_schema) and user-defined schemas. This approach is often preferred in scripts and cross-platform tools because it works the same way across different database systems, not just in PostgreSQL.

Below are screenshots that demonstrate how to use 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.

List schemas in PostgreSQL using dbForge GUI

While the command line remains a classic 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 code to your 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 PostgreSQL schema list in the Bicyclestore database, you can choose 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.

How to select a schema in PostgreSQL

To select a schema in PostgreSQL, set the schema context using the SET search_path command. PostgreSQL does not have a direct “USE schema” command like some other databases, but you can achieve the same result with SET search_path.

Working with schema context: SET search_path

The Postgres select schema operation is done using the following command:

SET search_path TO schema_name;

Example:

SET search_path TO production;
SELECT * FROM products;

Here, the SET search_path command runs a PostgreSQL select schema operation so that any query will resolve objects from the production schema by default.

You can also perform a select schema PostgreSQL operation by using a schema-qualified table name. For example:

SELECT * FROM production.products;

This approach doesn't change the search path but makes the schema explicit in every query, ensuring PostgreSQL always retrieves data from the correct location.

How to display all schemas with additional information

We already know how to conjure the list of all schemas in a database. However, understanding the overall architecture requires more than just schema names. This section will demonstrate how to augment 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 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 available in each schema:

How to show schema size information

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

SELECT
  n.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 n.nspname NOT IN ('pg_catalog', 'information_schema')
GROUP BY n.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.

How to find tables in a specific schema

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

SELECT tablename
FROM pg_tables
WHERE schemaname = 'production';

List stored functions or procedures in a particular schema

The next stop of 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's execute this query to retrieve all the procedures from the sales schema:

Conclusion

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 making PostgreSQL list schemas 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!