Display Available Tables in PostgreSQL

Posted on

Another common problem which is faced when accessing any type of database is how to display available tables in PostgreSQL ?.  By all means, it is very easy to do it with the help of GUI (Graphical User Interface) tools or utilities such as PgAdmin.

But it will be very difficult if we try to access database in a text mode. Especially in this article context, how to access PostgreSQL database server, more over a specific database and then retrieve all of the available tables in that database. We can do it by following several steps informed below :

1. First of all, we need to access the database in a text mode way. Below is how to do it from a PostgreSQL command console. Connect and log in first from the bash prompt which is representing the CLI (Command Line Interface) or text mode :

user@hostname:~/share$ psql -Upostgres
Password for user postgres:
psql (9.5.3, server 9.3.10)
Type "help" for help.

postgres=#

2. After successfully logged in to PostgreSQL command console. Off course, we need to choose the database where all tables reside. It can be accomplished by reading the following article in this link.

3.  Once we connected to the database where the tables which is going to be displayed or to be accessed had success, just type the following command to display all available tables in the database which is already connected :

Supposed, if we connect to a database named ‘testing’. as shown below :

postgres=# \c testing;
psql (9.5.3, server 9.3.10)
You are now connected to database "testing" as user "postgres".
testing=# 

We can continue on displaying tables which are available inside the selected database by typing the following command :

database_name=# \dt
\dt : It is a command executed in PostgreSQL command console which is used to display tables available in the selected database which is named database_name. We can imagine it dt as an abbreviation of display table to make it a lot easire to be remembered.

Below is the output of the above command execution :

testing=# \dt
List of relations
Schema |     Name     | Type  |  Owner
--------+--------------+-------+----------
public | applications | table | postgres
public | users        | table | postgres
(2 rows)

In the above output display of the command, we can see that there are two tables available in database testing. Those tables are applications and users.

Leave a Reply