How to List PostgreSQL Databases and Tables using psql

When administering PostgreSQL database servers, one of the most common tasks you will likely perform is listing the databases and their tables.

PostgreSQL comes with an interactive tool called psql, which allows you to connect to the server and run queries against it. When using psql you can also take advantage of its meta-commands. These commands are useful for scripting and command-line administration. All meta-commands begin with an unquoted backslash and are also known as backslash commands.

This tutorial explains how to show databases and tables in a PostgreSQL server using psql.

Listing Databases #

You can connect to the PostgreSQL server using the psql command as any system user. Depending on the server configuration, the user may need to enter its password to connect to the psql terminal. To access the psql terminal as the user you are currently logged in, simply type psql.

When the PostgreSQL package is installed, an administrative user named “postgres” is created. By default, this user can connect to the local PostgreSQL server without a password.

To access the psql terminal as user “postgres”, run:

sudo -u postgres psql

From within the psql terminal execute \l or \list meta-command to list all databases:

\l

The output will include the number of databases, name of each database, its owner, encoding and access privileges:

                              List of databases
   Name    |  Owner   | Encoding | Collate |  Ctype  |   Access privileges   
-----------+----------+----------+---------+---------+-----------------------
 odoo      | odoo     | UTF8     | C       | C.UTF-8 | 
 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
(4 rows)

PostgreSQL server has three databases created by default, template0, template1, and postgres. The first two are templates that are used when creating new databases.

If you want to get information about the sizes of the databases, default tablespaces, and descriptions use \l+ or \list+. The database size is shown only if the current user can connect to it.

To get a list of all databases without accessing the psql shell, use the -c switch as shown below:

sudo -u postgres psql -c "\l"

Another way to list the databases is to use the following SQL statement:

SELECT datname FROM pg_database;

Unlike the \l meta-command the query above will show only the names of the databases:

  datname  
-----------
 postgres
 odoo
 template1
 template0
(4 rows)

Listing Tables #

To list all the tables of a particular database first, you need to connect to it using the \c or \connect meta-command. The user you are logged in as to the psql terminal must be able to connect to the database.

For example, to connect to the database named “odoo” you would type:

\c odoo

Once the database is switched, use the \dt meta-command to list all database tables:

The output will include the number of the tables, the name of each table and its schema, type, and owner:

                              List of relations
 Schema |                        Name                         | Type  | Owner 
--------+-----------------------------------------------------+-------+-------
 public | base_import_import                                  | table | odoo
 public | base_import_mapping                                 | table | odoo
 public | base_import_tests_models_char                       | table | odoo
 ...
 public | web_editor_converter_test_sub                       | table | odoo
 public | web_tour_tour                                       | table | odoo
 public | wizard_ir_model_menu_create                         | table | odoo
(107 rows)

If the database is empty, the output will look like this:

No relations found.

To get information about the sizes of the tables, and descriptions use \dt+.

Conclusion #

You have learned how to list PostgreSQL databases and tables using the psql command.

Feel free to leave a comment if you have any questions.