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.

There are three ways to list them, and which one you want depends on where you are working: psql for a quick look, a catalog query when you need to filter or script it, and pgAdmin if you would rather not use a terminal. All three are below.

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.

List PostgreSQL Schemas in pgAdmin

If you would rather not use a terminal, pgAdmin lists schemas in its browser tree. Expand Servers, then your server, then Databases, then the database you want, then Schemas. Every schema in that database appears as a node, and expanding one shows its tables, views, functions and sequences.

Two things are worth knowing. The tree hides system schemas such as pg_catalog and pg_toast unless you turn them on under File, then Preferences, then Browser, then Display. And the tree is scoped to one database at a time, exactly like \dn, so switching databases means expanding a different node.

For anything you want to filter or reuse, open the Query Tool and run the catalog query above instead.

How to see a single table's schema

Listing schemas and reading a table's structure are different jobs, and the second is what most people actually want when they say "check the schema" of something.

In psql:

\d table_name

That returns the columns, their types, defaults, indexes and constraints. Use \d+ table_name to add storage settings and column comments.

From SQL, which works in any client:

SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'your_table'
ORDER BY ordinal_position;

Add AND table_schema = 'your_schema' when the same table name exists in more than one schema, which is common once you use schemas seriously.

How to check schema sizes

Useful before a migration, and the fastest way to find what is actually filling a database:

SELECT nspname AS schema,
       pg_size_pretty(sum(pg_total_relation_size(c.oid))) AS size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'm')
GROUP BY nspname
ORDER BY sum(pg_total_relation_size(c.oid)) DESC;

pg_total_relation_size counts the table, its indexes and its TOAST data, so the numbers match what the disk actually holds. For a per-table breakdown, see how to analyze PostgreSQL table size.

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;

FAQ

Dropping a schema is not reversible, so it is worth knowing you have a recent dump before you run it. Our complete guide to PostgreSQL backup covers how to take one and, more importantly, how to check it restores.