How to list all Databases and Tables using PSQL?

PSQL is also known as PostgresSQL relational database management system (RDBMS). It is created by a global volunteer team that is not governed by any company or private entity. It is open-source software for free access to its source code. This command-line tool has a strong reputation for efficiency, reliability, data integrity, and robustness.
In this article, we will discuss how to list all the databases and tables using PSQL along with necessary details.
First of all, we need to login to access databases and tables in PSQL. The prompt for logging into PSQL as superuser is in the format “-#” and for admin it is “->”. The directory named “data_directory” indicates the location of the databases.

How to list all databases?

The command “\list” or “\l” is used to list all databases. The shorthand for “\list is \l”.


The results show database name, owner, encoding method used, access privileges and no of rows selected, etc.

Figure 1: List of all databases

If you are comfortable with SQL statements then you can use the following SQL statement to list all databases.

SELECT datname FROM pg_database;
Figure 2: List of all databases using SQL statement.

How to list all tables?

You cannot see tables of any database until unless you have not established the connection to it. To list tables of any database first you need to connect to that particular database. If you are in a database and you want to see tables of another database you need to switch to another database using the following command. “\c” is short form of “\connect”.

\connect <database_name_say_centrality> 
\c <database_name_say_centrality>

Type the command “\dt” to list all tables in a current database.


Furthermore, you might have placed tables into a schema which is either not in your “search path” or the “default tables”. Thus these tables do not show up using “\dt”.
To fix this run the following command in which we need to provide search path name and database whose path we are trying to modify.

alter database <database_name_say_centrality> set search_path=<say_appuals>, public;

“\dt+” command will list all tables in all the schemas in the current database, in the current “search path”.

Figure 3: List of tables from all the schema in the current database and in current search_path

If you are more good at SQL statements, you can get a list of tables using “information_schema”.
The following command will list tables that are created by you.

SELECT table_name FROM information_schema.tables WHERE table_schema='public'

The following command will show tables and views which belongs to particular schemas.

select * from information_schema.tables where table_schema not in ('information_schema', ''pg_catalog'’)

The following command will show tables in a particular schema.

select a.table_name from information_schema.tables a where a.table_schema = '<name_of_schema>' and a.table_type = 'BASE TABLE' order by a.table_name;

Kevin Arrows

Kevin Arrows is a highly experienced and knowledgeable technology specialist with over a decade of industry experience. He holds a Microsoft Certified Technology Specialist (MCTS) certification and has a deep passion for staying up-to-date on the latest tech developments. Kevin has written extensively on a wide range of tech-related topics, showcasing his expertise and knowledge in areas such as software development, cybersecurity, and cloud computing. His contributions to the tech field have been widely recognized and respected by his peers, and he is highly regarded for his ability to explain complex technical concepts in a clear and concise manner.