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
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
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
sudo command allows you to run commands as another user.
From within the psql terminal execute
\list meta-command to list all databases:
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
\list+. Database size will be 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;
\l meta-command the query above will show only the names of the databases:
datname ----------- postgres odoo template1 template0 (4 rows)
To list all the tables of a particular database first you need to connect to it using the
\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:
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
You have learned how to list PostgreSQL databases and tables using the
Feel free to leave a comment if you have any questions.