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”.

\list
or
 \l

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

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

SELECT datname FROM pg_database;

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> 
or 
\c <database_name_say_centrality>

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

\dt

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”.

\dt+

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;