While working with PostgreSQL database servers, there is much useful application available for administering databases like pgAdmin4, phpPgAdmin, and SQL Workbench. A Postgres process contains multiple databases and is stored in a separate set of files under the server’s data directory.
The Postgres server also comes with psql utility for database administration via command-line. This article will help you to how to list databases in PostgreSQL server along with all tables in a single database.
Listing Databases in PostgreSQL
First connect to PostgreSQL terminal with psql command as user “postgres”. The default installation doesn’t require any password until specified manually. You can execute sudo command as below to directly connect to psql terminal as postgres user:
sudo -u postgres psql
Once you are connected to the psql terminal, type \l to list all available databases.
postgres=# \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)
You can also use \list to find similar results, but to find extended details use \l+\list+.
postgres=# \list+
With this command, you will find the additional details of the PostgreSQL database like size, tablespace, and description.
The postgres also provides an traditional SQL like statement to show all databases in Postgres. To determine the set of existing databases, fetch records from the pg_database system catalog, for example:
postgres=# SELECT datname FROM pg_database;
Output:datname ----------- postgres template1 template0 myDatabase (4 rows)
Listing Tables in Postgres Database
To list tables of any database first you connect to that database and then view tables in that database. The first command will connect you with the database (example: myDatabase) to which you want to see tables:
postgres=# \c myDatabase
Once you’ve connected to a database, you will see the database name in the prompt. Then execute the \dt meta-command to list all the tables in current database.
myDatabase=# \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)
Conclusion
In this quick guide, you have learned to list databases in the PostgreSQL database server. Also found instructions to connect the database and list available tables in the database.