PostgreSQL allows us to perform operations on the databases and tables, such as creation, updation, deletion, etc. In Postgres, performing any task on an existing database or table requires the name of that particular database or table. However, trying to remember the names of all of your PostgreSQL databases and tables is not an easy task. Therefore, Postgres provides simple commands to list all your databases and tables to deal with such scenarios.
This blog will show you how simple commands/statements are used to list all your Postgres databases and tables. So, let’s start!
How to Show a List of All Databases and Tables in PostgreSQL?
To get the list of databases, the “\l” command and pg_database catalog are used in Postgres. Let’s learn how to find the list of all databases using the “\l” command in Postgres:
Open SQL Shell:
Firstly, launch the psql, and provide the required details to log in:
Once logged in, you will get an interface similar to the above one.
1) Show Databases Via \l:
To show the list of all the databases, users must execute the “\l” command as follows:
\l
The output snippet shows the list of all the available databases, including default/system databases.
Note: Execute the “\l+” command to get the list of databases with more details like database size, description, etc.
2) Show Databases Via \list:
Alternatively, you can execute the “\list” command as follows:
\list
The list of databases contains all the default and user-defined databases.
3) Show Databases Via pg_catalog:
Run the below-provided command to get the list of databases using the pg_database catalog:
SELECT oid, datname FROM pg_database;
The output snippet shows the list of all the available databases.
Now, let’s learn how to show the list of all the tables in Postgres.
Connect to a Database
Firstly, you need to connect to a particular database; for this purpose, you must execute the “\c” command:
\c example;
Once connected to the database of your choice, now, you can show the list of tables available in that particular database via the following methods:
1) List Tables Via “\dt”
Executing the below-provided command will show you the list of tables available in the “example” database:
\dt;
The output shows that the “\dt” command successfully shows the list of relations.
Note: Execute the “\dt+” command to show the list of relations with more details, like description, access method, table size, etc.
2) List Tables Via pg_catalog
Alternatively, you can use the pg_catalog schema to show the list of all tables:
SELECT * FROM pg_catalog.pg_tables;
The output snippet shows all the tables, including system and user-defined tables. To show only user-defined tables, you must use the WHERE clause as follows:
SELECT tablename
FROM pg_catalog.pg_tables
WHERE schemaname NOT IN ('pg_catalog','information_schema');
This is how you can show the list of all the databases and tables in Postgres.
Conclusion
In PostgreSQL, the “\l”, “\list”, and “pg_catalog” are used to show the list of databases, while the “\dt” command and “pg_catalog.pg_table” schema is used to show the list of relations. Use the “\l+” and “\dt+” commands to get the more detailed information regarding the databases and tables. This Postgres blog explained how to show the list of databases and tables in Postgres via practical demonstration.
