List all databases in PostgreSQL

In PostgreSQL, you can use two methods to list all databases in a PostgreSQL server:

• Use \l or \l+ to list all databases in the psql tool.
• Query all databases from the pg_database table.

Use \l to list databases

This example demonstrates the steps to use the psql tool log in to the database and list all databases. Please follow the steps below:

1. Log in to the PostgreSQL server as the postgres user:

   [~] psql -U postgres
   psql (14.4)
   Type "help" for help.
   

   Note: You can also log in as any other user with appropriate database privileges.

2. List all databases using the \l command as follows:

   \l
   

                                List of databases
      Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges
   -----------+----------+----------+---------+---------+-----------------------
    postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
    template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
              |          |          |         |         | postgres=CTc/postgres
    template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
              |          |          |         |         | postgres=CTc/postgres
    testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
    testdb2   | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
   (5 rows)

3. If you want to see more information about the database, use the \l+ command as follows:

   \l+
   

                                                                  List of databases
      Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   |  Size   | Tablespace |                Description
   -----------+----------+----------+---------+---------+-----------------------+---------+------------+--------------------------------------------
    postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |                       | 8529 kB | pg_default | default administrative connection database
    template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +| 8377 kB | pg_default | unmodifiable empty database
              |          |          |         |         | postgres=CTc/postgres |         |            |
    template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +| 8529 kB | pg_default | default template for new databases
              |          |          |         |         | postgres=CTc/postgres |         |            |
    testdb    | postgres | UTF8     | C.UTF-8 | C.UTF-8 |                       | 8897 kB | pg_default |
    testdb2   | postgres | UTF8     | C.UTF-8 | C.UTF-8 |                       | 8545 kB | pg_default |
   (5 rows)

   As you can see, there are more three columns Size, Tablespace and Description than \l.

Query databases from pg_database table

In addition to using the \l+ and \l commands above, you can also query all databases from the pg_database table.

The pg_database table is a table built into PostgreSQL that stores all the databases.

SELECT datname FROM pg_database;

 datname
-----------
 postgres
 testdb
 template1
 template0
 testdb2
(5 rows)

Conclusion

There are two methods to list all the databases in the PostgreSQL server:

• Use \l or \l+ commands in psql tools to list all databases.
• Query all databases from the pg_database table.

In MySQL, you can use the SHOW DATABASES command to list databases.