How to List PostgreSQL Databases and Tables Using psql?

PostgreSQL is a relational database management system to perform various operations such as creating, modifying, and deleting databases, tables, roles, and many more. These operations are performed in Postgres SQL Shell, referred to as psql.

In this PostgreSQL guide, we will list the possible methods to list PostgreSQL Databases and tables using psql.

The content that illustrates this guide is as follows:

Let’s take a tour of this guide.

Prerequisite: How to Create Databases and Tables Using psql?

It is a prerequisite to creating a database and table after accessing the psql environment. Before creating databases, you must ensure that PostgreSQL is installed on your system.  For this, follow the steps provided below:

Step 1: Login as a postgres User

To switch the user account, the “-u” option is utilized with the “postgres” as below:

$ sudo -i -u postgres

Step 2: Enter the psql Shell

Type the “psql” that navigates to the “postgres” shell where you can run PostgreSQL queries:

$ psql

Step 3: Create a Database

Now, using the “CREATE DATABASE” keyword, a database named “dbstd” is created as follows:

$ CREATE DATABASE dbstd

To connect the particular database, the “\c” is utilized with the database name such as “dbstd”:

$ \c dbstd

Step 4; Create Table and Insert Data

In this current database, you can create a table such as “tbl_std” through “CREATE TABLE” and specify the columns name as “id”, “name” and “role” as seen below:

$ CREATE TABLE tbl_std (id serial PRIMARY KEY, name varchar, role varchar);

After creating a table, you can insert values as “johnson” and “employe” in the “name” and “role”

columns as below:

$ INSERT into tbl_std (name, role) VALUES ('johnson', 'employe');

The output shows that values have been successfully inserted in the existing table “tbl_std”.

How to List PostgreSQL Databases Using psql?

In PostgreSQL, the list of current databases can be visualized through the “\l” command. After executing this command, it displays the information of databases such as “Name”, “Owner”, “Encoding”,  and many more as seen below:

$ \l

The output shows the “Name”, “Owner”, “Encoding”, “Collate”, “Ctype” and “Access privileges” of all the databases. Additionally, the existing databases can be seen at the left-most of the window through the psql prompt.

Let’s explore the PostgreSQL table:

How to List PostgreSQL Table Using psql?

Using the “psql” prompt, you can list out all the existing tables through the “\dt” command in the particular database. In our case, an existing table “tbl_std” is already created in the database “dbstd”  that can be visualized through the below command:

$ \dt

After executing the above command, the “Schema”, “Name”, “Type” and “Owner” are presented in the tabular form.

Bonus Tip: How to Get the Content of the PostgreSQL Tables Using psql?

To fetch all information of a specific table, the “SELECT” statement can be used with “*” by specifying the table name as “tbl_std”:

$ SELECT * from tbl_std;

The output returns three columns “id”, “name” and “role” containing values “1”, “johnson” and “employe” in the “tbl_std”.

List the Particular Column From Table

To display the specific column from the existing table, follow the “SELECT <Column> FROM <Table>” format:

$ SELECT Name FROM tbl_std;

The output shows that values “johnson” stored in the “Name” column have been displayed from the “tbl_std”.

That is all from this guide.

Conclusion

Using the “psql” prompt, the “\l” and “\dt” commands are utilized to list out existing PostgreSQL databases and tables in a specific database. These commands are quite useful in reducing redundancy issues during the creation of new databases and tables. The “\l” command provides database information, including “Name”, “Owner”, and “Encoding”. The “dt” command displays the “Schema”, “Name”, “Type”, and “Owner” information of existing tables. This guide has explained all methods to display PostgreSQL databases and tables using the psql shell.