How to List Databases and Tables in PostgreSQL

PostgreSQL is a powerful open-source relational database management system, known for its robustness and extensibility. As a database administrator or developer, one of the fundamental tasks you’ll often need to perform is listing databases and tables within PostgreSQL. In this step-by-step tutorial, we will walk you through the process of listing databases and tables using various methods and PostgreSQL commands.

Listing Databases in PostgreSQL

1. Open your terminal or command prompt.
2. To access the PostgreSQL command-line interface (psql), type the following command, replacing your_username with your actual PostgreSQL username:

   psql -U your_username 
   

   You will be prompted to enter your password. Once authenticated, you will enter the PostgreSQL interactive shell.

3. To list all the databases in PostgreSQL, use the following SQL command:

   
   \l
   
   

   Output:
                                List of databases
      Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
   -----------+----------+----------+---------+-------+-----------------------
    postgres  | postgres | UTF8     | en_IN   | en_IN |
    myDatabase| tecadmin  | UTF8     | en_IN   | en_IN | =CTc/tecadmin         +
              |          |          |         |       | tecadmin=CTc/tecadmin
    template0 | postgres | UTF8     | en_IN   | en_IN | =c/postgres          +
              |          |          |         |       | postgres=CTc/postgres
    template1 | postgres | UTF8     | en_IN   | en_IN | =c/postgres          +
              |          |          |         |       | postgres=CTc/postgres
   (4 rows)
   

4. Alternatively, you can use the following SQL query:

   
   SELECT datname FROM pg_database;
   
   

Listing Tables in a PostgreSQL Database

Now, let’s see how to list the tables within a specific database.

1. After connecting to psql as described in the previous section, select a specific database using the following command, replacing your_database_name with the name of the database you want to explore:

   
   \c your_database_name
   
   

2. To list all the tables in the current database, use the following SQL command:

   
   \dt
   
   

   Output:
              List of relations
    Schema |    Name    | Type  |  Owner
   --------+------------+-------+----------
    public | results    | table | tecadmin
    public | roles      | table | tecadmin
    public | employee   | table | tecadmin
    public | users      | table | tecadmin
   (4 rows)
   

3. Alternatively, you can query the information schema to get the list of tables:

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

Conclusion

In this step-by-step tutorial, we’ve shown you how to list databases and tables in PostgreSQL using both the command-line interface (psql). These fundamental tasks are essential for any PostgreSQL administrator or developer, as they provide insight into the structure of your PostgreSQL environment.