PostgreSQL – Show All Databases and Tables Using PSQL

PostgreSQL has a bunch of GUI tools for the management and administration of databases and tables. However, you may require to learn psql commands to interact with the PostgreSQL server. Therefore, we will see how can we list all databases and tables in PostgreSQL using the psql shell. So, let’s get started!

Also read: PostgreSQL – A Complete Introduction, History, and Features

Before you begin

Postgresql has a command for almost any operation and there are a bunch of alternative ways of doing the same thing. In our case, there are two ways to list all the databases and tables using the psql commands.

This tutorial is made for beginners and of course, there is no rocket science here. It is one of the simplest things you can do in PostgreSQL. Just make sure you can log in to the psql shell correctly, that’s it!

Now let’s see the syntax to list all databases and tables.

Also read: PostgreSQL – How to Create a Database

Get a List of All Databases

Following is the syntax to list all databases on the PostgreSQL server.

\l 
or
\l+Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

As I mentioned earlier, this is one of the simplest things to do in PostgreSQL. You don’t have to write a command as you do in MySQL or other databases.

As you can see in the output above, the l+ command gives us some more information about the databases such as size, tablespace, and description.

Alternatively, if you want to get the list of all databases programmatically, you can use the following syntax. And of course, it works in psql too.

SELECT datname FROM pg_database;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

This syntax is useful when you want to get the list of the database using a programming language such as PHP, Java, Python, etc. Note that, the pg_database is a system catalog in PostgreSQL.

Get a List of All Tables in a Database

Now, to list all tables in a database, we can use two ways. Let’s see the simple one first.

To use the simple way, you need to connect to the database using the command \c dbname.

\c pagila;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Now write the following command to list the tables.

\dt 
or 
\dt+Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that, you must be connected to the database in order to list the tables.

Alternatively, you can use another way to get the list of tables using the programmatic way.

SELECT * FROM information_schema.tables 
WHERE table_catalog='pagila' 
AND table_type='BASE TABLE'
AND table_schema NOT IN ('pg_catalog','information_schema');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Here, the details of all tables reside in the information_schema database. Apart from the base tables, there are multiple views are also present in the database which we hide in the result using the WHERE clause.

As you can see above, we get detailed information about the tables.

Conclusion

That’s it! You now know how to list databases and tables in PostgreSQL. With this information, you can effectively manage your data in PostgreSQL and ensure that your data is organized and easily accessible.