intro
A single PostgreSQL database typically contains multiple schemas. In this guide, you will explore three different PostgreSQL list schemas approaches.
When working with PostgreSQL, you will often need to list the schemas available in your selected database. This is a common task for PostgreSQL users and database administrators. There are several Postgres list schemas approaches, depending on your tools, preferences, and level of experience.
In this article, you will see three different methods:
Let’s dive into how to list schemas in PostgreSQL!
Listing Schemas with the psql Command-Line Tool
psql is the “PostgreSQL interactive terminal” that lets you interact with a PostgreSQL server via the command line. With psql, you can:
In detail, psql provides the \dn command to list schemas in a PostgreSQL database. Follow the steps below to learn how to use psql to list schemas:
Step 1: Launch the terminal.
Step 2: Connect to your PostgreSQL server and database with the following psql command-line tool:
1
psql -U <username> -d <database_name>
Replace <username> with your PostgreSQL username and <database_name> with the name of the database you want to inspect. You will be prompted to enter the password. Type it and press Enter to connect to the database.
Security tip: Use a .pgpass file to avoid your credentials from being logged in the terminal history and to avoid typing them every time.
Step 3: List the schemas in the <database_name> database with:
1
\dn
This command will return a table containing all the Postgres schemas in the connected database, along with their owners:
Note: Non-local temporary schemas are automatically suppressed by the \dn command.
In this article, we will use a PostgreSQL database named company to demonstrate how to list schemas.
Use \dn+ to get additional information about each schema, such as access privileges and its description:
Keep in mind that the \dn (or \dn+) command accepts an optional regular expression as a parameter. If provided, only schemas whose names match the pattern will be listed.
For example, to list only schemas that start with the letter “e”, run:
1
\dn e*
The result will be as follows:
How to List All Schemas in Postgres with a Query
If you are not a fan of using the command line, do not worry. You can also list Postgres schemas by running an SQL query.
In particular, PostgreSQL stores schema metadata in the pg_catalog.pg_namespace system catalog. Each row in that table represents a schema in the current database.
Thus, to retrieve the list of schemas, run the following query:
1
SELECT * FROM pg_catalog.pg_namespace;
This returns a table containing details about all schemas, including their names, owners (nspowner is the Object Identifier — OID — of the owner of the namespace), and access privileges:
The column you should be most interested in is nspname, which is the column that holds the name of each schema.
However, this table also includes internal schemas like pg_catalog and information_schema, which are used by PostgreSQL for system operations. If you want to list only user-defined schemas, you can filter them out with the following query:
1
SELECT nspname
2
FROM pg_catalog.pg_namespace
3
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
4
AND nspname NOT LIKE 'pg_toast%';
In this case, the result will be as follows:
Explore PostgreSQL Schemas in a Visual Database Client
The simplest way to view all schemas in PostgreSQL is by using a graphical database client like DbVisualizer. This powerful tool lets you visually connect to and manage your PostgreSQL databases.
In detail, DbVisualizer offers extended support for PostgreSQL-specific object types and features, and it works with over 50 other databases.
To list Postgres schemas in DbVisualizer, complete the following steps:
Step 1: Configure a connection to your PostgreSQL server as explained in the official docs.
Step 2: Select your target server in the “Connections” menu on the left, right-click on it, and choose the “Connect” option:
Step 3: Expand the “Databases” dropdown and select your database of interest (in this case, company):
Step 4: Open the “Schemas” dropdown to see all available schemas, or open them in a new tab:
Awesome! With just a few clicks, DbVisualizer makes it easy to list PostgreSQL schemas.
Conclusion
In this article, you learned that the Postgres list schemas task is straightforward, and there are at least three effective ways to do it:
In particular, a powerful PostgreSQL-compatible client like DbVisualizer lets you view and manage schemas visually. It also offers advanced features such as query optimization tools, ERD-style schema generation, and robust import/export options. Download DbVisualizer for free!
FAQ
How to list users in PostgreSQL?
To list users in PostgreSQL, you can connect to your database and run the following query:
1
SELECT usename FROM pg_catalog.pg_user;
This will return a list of all users in your PostgreSQL instance. For more practical methods, read our detailed guide on how to list users in PostgreSQL.
How to list databases in PostgreSQL?
To list databases in PostgreSQL, you can connect to your PostgreSQL server in psql and then launch the following psql command:
1
\l
This will display a list of all databases in your PostgreSQL instance. For other approaches, be sure to check out our tutorial on listing databases in Postgres.
How to list all tables in a schema in Postgres?
To list all tables in a specific PostgreSQL schema, run this query:
1
SELECT tablename FROM pg_catalog.pg_tables
2
WHERE schemaname = 'your_schema_name';
Where 'your_schema_name' is the name of your target schema. Instead, in psql, you can achieve the same result with:
1
\dt your_schema_name.*
How to use pgAdmin to view the list of schemas in PostgreSQL?
To view the list of schemas in PostgreSQL using pgAdmin:
You can also right-click on Schemas to refresh or explore schema details further.
Are there any security concerns when listing schemas in PostgreSQL?
In PostgreSQL, users can only see schemas they have access to. If a user lacks privileges on certain schemas, those schemas will not appear in their listings. Granting broader access may expose schema names or metadata unintentionally, so it is best practice to always follow the principle of least privilege.
