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.
The simplest way to list schemas in a PostgreSQL database is through the
\dn command in the psql command-line interface.
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
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:
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.
Below you will also find the most commonly used and valuable commands.
How to view the details of a specific schema in PostgreSQL
How to list tables within a schema in PostgreSQL
How to create a new schema in PostgreSQL
CREATE SCHEMA new_schema_name;
How to drop a schema in PostgreSQL
DROP SCHEMA schema_name;
Free 7-day trial. No credit card required.
Have a question? Need help getting started?
Get in touch via chat or at [email protected]