How to List All Schemas in the PostgreSQL Database: An Ultimate Guide

PostgreSQL, an object-relational database management system, supports SQL for querying relational data and JSON for non-relational data. This flexibility helps you handle complex data types for applications ranging from web apps to enterprise systems.

Effective schema management is essential to fully utilize PostgreSQL's capabilities in application development. With proper schema management, you can ensure data integrity and enhance scalability in complex systems.

Efficient database administration, whether for troubleshooting, performance optimization, or access auditing, requires a clear overview of all database schemas. This article provides details of the different ways to gather PostgreSQL list schemas in databases.

What Is PostgreSQL Schema?

PostgreSQL schemas are logical containers that allow you to organize database objects like tables, views, data types, and operators. Objects like views, materialized views, tables, sequences, and foreign tables must have unique names within a schema due to a shared namespace.

While objects of the same type cannot share names in a single schema, identical object names can exist across different schemas. For instance, both schema_info and my_schema can contain a table named my_table without conflict.

Each database has a public schema by default. You can also create your schemas with specific ownership and privileges using the CREATE SCHEMA command. If you no longer need a schema, you can remove it using the DROP SCHEMA command. If the schema contains objects, you can drop them using the CASCADE option.

To create or access objects within a schema, use the following qualified name format:

schema.object

Example:

my_schema.my_table

To create a table in my_schema, the syntax will be:

CREATE TABLE my_schema.my_table (
    id INT,
    name TEXT
);

However, writing fully qualified names, such as specifying both schema and object names, in PostgreSQL is tedious. To simplify this, PostgreSQL allows the use of unqualified names, such as just the table name. It also leverages a search path to determine which schema to search for a matching object. This search path feature helps you manage object references without explicitly specifying schema names. 

Methods to List All Schemas in PostgreSQL Database

There are various methods you can use to list all schemas in PostgreSQL, each providing different levels of details and filtering options.

Common Prerequisites:

• Download and install PostgreSQL's latest version.
• Add PostgreSQL to the system path.

Using psql Commands

psql is PostgreSQL’s interactive CLI that facilitates interaction with the database. It allows you to execute SQL queries to manage objects and perform administrative tasks.

psql also has many meta-commands, which are the commands prefixed with a backslash (\). These meta-commands enable you to perform tasks such as listing tables or viewing table structures without querying the database.

Steps to Use psql to Allow PostgreSQL List Schemas in Database

1. Open your command prompt and connect to your PostgreSQL instance:

psql -U postgres

2. Connect to a specific database with the following command:

psql -h hostname -U username -d database_name

3. List all databases:

\l

4. Choose a database from the list and switch to it:

\c database_name

5. Retrieve all schemas with their owners:

\dn

You can also use the \dn+ command to get more information, such as access privileges and descriptions of the available schemas.

Using information_schema

The information_schema is an ANSI-SQL standard set of read-only views that is automatically created in every database, including PostgreSQL. It provides metadata about the objects in your current database. The initial user in the PostgreSQL database cluster owns this schema. 

By querying the information_schema, you can access details about tables, columns, data types, privileges, constraints, and more. By default, information_schema is not included in the schema search path; you must use fully qualified names when referencing its objects. This approach helps prevent naming conflicts, as many objects in information_schema use general names like tables or columns.

Steps to Use information_schema for Allowing PostgreSQL List Schemas in Database

1. Connect to the psql terminal.

2. Execute the following command:

SELECT schema_name
FROM information_schema.schemata;

Here, schemata is a view that includes metadata about all schemas in the PostgreSQL database.

Using pg_catalog

Each PostgreSQL database contains a pg_catalog schema, which contains the system tables and all the predefined data types, operators, and functions.

pg_catalog is always included in the search path by default. If it is not explicitly included in the search path command, PostgreSQL always searches pg_catalog first, ensuring that built-in system names are readily accessible.

Steps to Use pg_catalog to List All the PostgreSQL Schemas 

1. Access the psql CLI directly through your command prompt. 

2. Execute the following query:

SELECT nspname AS schema_name
FROM pg_catalog.pg_namespace;

In this query, nspname is a column in the pg_catalog.pg_namespace system catalog. The column includes the actual schema names from the pg_namespace table. 

Listing Schemas with Privileges in PostgreSQL

To list PostgreSQL schemas along with the privileges granted to them, you can utilize views from the information_schema. Specifically, you can use information_schema.schemata for schema details and information.schema.schema_privileges to retrieve information about permissions associated with each schema.

Execute the following SQL query in the psql terminal to retrieve a list of PostgreSQL schema names with corresponding privileges: 

SELECT
    schemata.schema_name,
    schema_privileges.grantee,
    schema_privileges.privilege_type
FROM
    information_schema.schemata
LEFT JOIN
    information_schema.schema_privileges
    ON schemata.schema_name = schema_privileges.schema_name
ORDER BY
    schemata.schema_name, schema_privileges.grantee;

This query performs a LEFT JOIN between information_schema.schemata and information_schema.schema_privileges to combine schema information with privilege details.

The LEFT JOIN ensures that schemas without granted privileges are also included in the results with NULL values. Then, the result set is ordered by schema_name and then by grantee, providing a structured view of who has what access to each schema.  

Listing Schemas with Their Sizes

You can list PostgreSQL schemas along with their size using the following command:

SELECT nspname AS schema_name,
       pg_size_pretty(SUM(pg_total_relation_size(pg_class.oid))) AS size
FROM pg_catalog.pg_namespace
JOIN pg_catalog.pg_class ON pg_class.relnamespace = pg_namespace.oid
GROUP BY schema_name;

The query lists the total size of each schema in a PostgreSQL database by joining pg_catlog.pg_namespace with pg_catalog.pg_class. 

The pg_catalog.pg_namespace is a system catalog that allows you to store metadata about the schemas. In contrast, pg_catalog.pg_class helps you store table and index information. The above query calculates the total disk space used by all objects in each schema using the pg_total_relation_size function.

The pg_size_pretty function enables you to convert the total size from bytes into a human-readable format such as KB, MG, or GB, and the results are grouped by schema name.

How Do You List All Schemas in the PostgreSQL Database Using Python?

To retrieve all schemas from a PostgreSQL database with Python, you can use the psycopg2 library. Here are the detailed steps:

1. Ensure you have PostgreSQL credentials. 

2. Download and install the latest Python package from the official website. 

3. Open your command prompt and install psycopg2 using the pip command:

pip install psycopg2

4. Execute the following Python program in a Python IDE or Online Jupyter Notebook:

Initially, you must import the Python’s psycopg2 library.

import psycopg2

Connect to your PostgreSQL database instance by replacing the given placeholders with your actual database credentials.

conn = psycopg2.connect(
    dbname="postgres_database_name", 
    user="postgresDB_username", 
    password="postgresDB_password", 
    host="host_address", 
    port="port_number"
)

Create a cursor object in the Python psycopg2 library. This cursor acts as a pointer to interact with the database and execute SQL commands.

cur = conn.cursor()

Execute the below query to select non-system schema names from the information_schema.schemata.

cur.execute("""
    SELECT schema_name
    FROM information_schema.schemata
    WHERE schema_name NOT IN ('information_schema', 'pg_catalog');""")

You can add the following code snippet to fetch and print all schema names:

schemas = cur.fetchall()
for schema in schemas:
    print(schema[0])

Once all the schemas are retrieved, you can close the cursor and connection to release the system resources.

cur.close()
conn.close()

With these steps, you can list all PostgreSQL schemas using the Python psycopg2 library.

Why Listing Schemas In PostgreSQL Is Useful?

• Understanding the Database Structure: With PostgreSQL list schemas in the database, you can analyze the high-level structure and organization of database objects. This facilitates better scalability and database maintainability.‍
• Identifying Available Schemas: Running a query to list schemas allows you to quickly identify which schemas exist in the database. This is useful in multi-schema environments where different teams or applications might use separate schemas for isolating work.‍
• Access Control and Permissions: Listing schemas provides an overview of available Postgres schemas. This visibility allows your administrators to grant or revoke permissions to users or roles on specific schemas. The outcome is enhanced data security by controlling access to sensitive information.‍
• Performance Monitoring: Understanding the distribution of data and objects across schemas helps you monitor the performance of PostgreSQL databases. It can help identify storage inefficiencies, optimize query performance, and improve resource management.‍
• Full-Text Search Index Availability: PostgreSQL full-text search allows you to search and query in natural language terms within text data stored in your database. You can use indexes like GIN or GiST for faster full-text searches. Listing schemas in PostgreSQL helps verify that these indexes are present in the correct schema for efficient querying.

How Airbyte Helps in PostgreSQL Schema Management?

Proper schema management is essential when transferring data to or from PostgreSQL. It helps maintain data consistency across different platforms and prevent conflicts during integration. Airbyte, a data movement platform, facilitates a smooth integration process with features that simplify and automate schema management.

For streamlined data movement, Airbyte offers 400+ pre-built connectors, including source and destination connectors for PostgreSQL. With a PostgreSQL source connector, you can extract data from PostgreSQL and load it into advanced analytics platforms.

Once you set up the pipeline between PostgreSQL and any other analytical platform in Airbyte, you can configure the schema settings. Following this, Airbyte enables you to check for changes in the PostgreSQL database schema automatically. For cloud users, it is checked every 15 minutes, and for self-hosted users, it is checked every 24 hours.

To utilize Airbyte for your specific use cases, you can connect with experts.

Conclusion

You have learned how to enable PostgreSQL list schemas in database using various methods. To efficiently access your PostgreSQL database schema, you can use psql commands, query information_schema views, or explore PostgreSQL’s system catalog tables. This comprehensive guide also helps you to identify objects within specific schemas and ensure better organization and security of your data.