You will often need to list the databases or even tables when working with databases. Like most database servers, Postgres has a command-line-based tool that allows you to manage your Postgres server.

This tool is psql. It allows you to type in queries, execute them and display the results. You can use psql to list databases and show tables in Postgres with some simple commands.

Getting Set Up

To connect to the server and list databases or tables, you’ll need to have PostgreSQL installed. Here are some helpful tutorials:

Listing Databases in PostgreSQL

Once you connect to the database using the psql command, you can list all the databases in the connected server using the following command.

        postgres=# \l

This is a shortcut for the \list command.

This command will list at least the three default databases: postgres, template0, and template1. postgres is the first database you will connect to if you have not yet created another database.

list of database in postgres on the terminal

You can create a new database using the following command.

        postgres=# create database dbname;

Once you have created the database, you will need to switch to it in order to insert data and perform other operations on it.

        postgres=# \c dbname

Listing Tables in PostgreSQL

After connecting to the database and creating tables, you may want to know what tables exist. You can do this by listing the tables using the psql command, \dt.

        dbname=# \dt

If no tables exist this command will show a Did not find any relations message. Otherwise, it will display the tables in a tabular format.

For example, create a table named users in a database called blog using the following command.

        blog=# create table users (
user_id serial PRIMARY KEY,
email VARCHAR ( 255 ) UNIQUE NOT NULL,
password VARCHAR ( 50 ) NOT NULL
);

If you list the tables using the \dt command, the output will be similar to the one shown below.

Terrminal showing list of tables in a Postgres database

Managing Your PostgreSQL Database

There are a couple of simple psql commands you can use to list databases and show tables in a PostgreSQL server. They are just two of the many commands that the psql tool offers.

However, if you want an easy-to-use GUI application to manage your PostgreSQL database, opt for the pgAdmin tool. Using pgAdmin, you can interact with your server without using the command line.