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:
- How to Install PostgreSQL on Windows.
- How to Install PostgreSQL on Ubuntu.
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.
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.
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.