How to List Schemas in PostgreSQL and Related Commands

Schema in PostgreSQL is nothing more than a folder in the sense of a standard operating system - it contains tables, views, and other objects typical of databases.

Schemes allow not only to organize data, but also to implement the level of control of individual users to the given schemes.

Primary Method to List Postgres Schemas

The simplest way to list schemas in a PostgreSQL database is through the \dn command in the psql command-line interface.

Code Snippet:

\dn

This command displays all schemas accessible to the user, providing a straightforward overview of the database's structure.

 List of schemas
  Name   |  Owner   
---------+----------
 public  | postgres
 sales   | john_doe
 hr      | jane_doe
(3 rows)

List PostgreSQL Schemas Using SQL Query

For a more detailed view, you can list schemas using a SQL query on the pg_catalog.pg_namespace system catalog.

SELECT nspname FROM pg_catalog.pg_namespace;

An alternative query is:

select schema_name
from information_schema.schemata;

This method lists the schemas and allows for further manipulation and filtering of the output, offering a more in-depth insight into the database’s organization.

Filtering and Customizing Schema Listings

To tailor schema listings to specific requirements, such as filtering by user or creation date, SQL queries can be modified accordingly.

SELECT nspname FROM pg_catalog.pg_namespace WHERE nspowner = (SELECT oid FROM pg_roles WHERE rolname = 'username');

This query, for example, lists schemas owned by a specific user.

Related Useful PostgreSQL Commands and Queries

Below you will also find the most commonly used and valuable commands.

How to view the details of a specific schema in PostgreSQL

\dn+ schema_name

How to list tables within a schema in PostgreSQL

\dt schema_name.*

How to create a new schema in PostgreSQL

CREATE SCHEMA new_schema_name;

How to drop a schema in PostgreSQL

DROP SCHEMA schema_name;