Listing Schemas in PostgreSQL

PostgreSQL: Listing All Schemas in a Database

Schemas in PostgreSQL help organize database objects into logical groups. This guide will show you how to list all schemas within a PostgreSQL database, using both the psql command-line tool and SQL queries. Listing schemas is useful for understanding the structure and organization of a database, especially in complex environments with multiple schemas.

Syntax:

To list all schemas in PostgreSQL, you can use either the \dn command within psql or query the pg_namespace system catalog

1. Using \dn Command in psql

The \dn command lists all schemas in the current database, showing schema names and owners.

\dn

2. Querying the pg_namespace System Catalog

The pg_namespace catalog contains information about all schemas in a PostgreSQL database, and you can query it to get a list of schemas.

SELECT nspname FROM pg_namespace;

Example 1: Listing Schemas Using \dn Command

Code:

-- Lists all schemas in the PostgreSQL database, along with their owners
\dn

Explanation:

• This command, when used in the psql command-line interface, displays all schemas within the current database along with their owners. It is a quick way to get a schema overview.

Example 2: Listing Schemas with a Query on pg_namespace

Code:

-- Retrieves the names of all schemas in the database
SELECT nspname AS schema_name FROM pg_namespace;

Explanation:

• The pg_namespace table holds metadata about schemas. Querying the nspname column provides the names of all schemas. Renaming the column to schema_name makes it more readable in the output.

Example 3: Filtering System Schemas from the List

Code:

-- Retrieves only user-defined schemas, excluding system schemas
SELECT nspname AS schema_name
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname <> 'information_schema';

Explanation:

• In PostgreSQL, system schemas (e.g., those starting with pg_ and information_schema) are often unnecessary in user-focused schema listings. This query filters out those schemas, showing only user-defined schemas in the database.

Important Notes:

1. Understanding Schemas:

• In PostgreSQL, schemas are logical containers for database objects (like tables, views, and functions). Each database comes with a default public schema that can store user-defined objects, but additional schemas are often created for organizational purposes.

2. Schema Privileges:

• Permissions for creating and accessing schemas can be managed with commands like CREATE SCHEMA, GRANT, and REVOKE. For example:

Code:

GRANT USAGE ON SCHEMA schema_name TO username;

This command gives the user username permission to access objects within schema_name.

3. System Schemas:

• PostgreSQL includes several system schemas by default, such as pg_catalog (for system catalogs) and information_schema (for ANSI-compliant schema information). User-defined schemas usually reside outside these system schemas.

All PostgreSQL Questions, Answers, and Code Snippets Collection.