In this tutorial, we will provide you with a way to show tables of a particular database in PostgreSQL.
Method 1 – Using psql Utility
One of the best ways to show the tables of a PostgreSQL database is to use the psql utility. Psql is a command-line utility that allows you to interact with your PostgreSQL cluster as you would in a front-end interface.
Using the psql command, you can view, connect, modify, and delete databases in short and readable commands. You can also create automated scripts and execute them in the psql interface.
For now, we are interested on learning how to get the tables of particular table.
Start by opening your command-line interface and run the command below to connect to your PostgreSQL server.
Replace the username with the target PostgreSQL user.
For example, to connect the server as the postgres user, we can execute the command:
The command will prompt you for the password of the specified user. Type the password and press RETURN.
You will now be logged in to the PostgreSQL command-line interface.
Next, connect to the database whose tables you wish to display with the \c command. The syntax is as shown:
For example, to connect the pagila database, we can run:
Once connected to the target database, we can list the database tables by running the command:
For example, to list the tables in the pagila database, we can run the command:
The command should return a list of all the tables in the pagila database.
Output:
PostgreSQL also allows you to fetch detailed information about the tables using the \dt+ command:
For example, in our pagila database:
The resulting output:
The command allows to get more information such as persistence, access method, size, and description.
Method 2 – Using information Schema
information_schema schema is a built-in schema that holds a lot of information about the server and the database stored.
You can check out our tutorial on the topic to discover more.
https://linuxhint.com/mysql-information_schema-examples/
We can use this schema to fetch the list of tables in PostgreSQL as well.
The query syntax is as shown:
To fetch the tables of a specific database, use the WHERE clause as shown:
For example, to fetch the table information from the information_schema in the pagila database, we can run:
The query above should return the table information of the selected schema.
The drawback of the above method is that it requires you to know the schema. It also returns a lot of unnecessary information about the tables.
Conclusion
In this article, you learned how to use the psql utility and the dt or dt+ command to get the tables of a particular database. We also explored how to use the information_schema to fetch the table information.
