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.
- 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.
It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.
https://www.w3resource.com/PostgreSQL/snippets/postgresql-list-schemas.php
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics