PostgreSQL stores the data in a way that is efficient, reliable, and easy to use and manage for a user. Huge companies use this database management system for security purposes to secure their valuable data from any intrusions or malware. In the earlier guide, we learned how to create tables in PostgreSQL using different techniques. In this article, we will be learning how to show the tables in the database we created earlier.
Different Methods for PostgreSQL Show Tables:
You have created a large project for a firm that includes many databases and tables in it, now you need to make changes in a specific table, and you can’t find it. PostgreSQL show tables will help you find the tables you are looking for. There is no specific command to show tables in PostgreSQL like we used to create tables or find the maximum value, but it can be done using two different methods:
- By SQL shell (psql).
- By pgAdmin4.
Show Tables Using SQL Shell (psql) in PostgreSQL:
SQL shell psql is a terminal where you can run queries, and they are directed to PostgreSQL. It occurs with the PostgreSQL setup when you download it. For opening the SQL shell, search for “SQL Shell (psql)” in your system. By doing this, the following screen will appear on your device:
Once you have entered the SQL Shell, move to the next step. The server is selected by default, i.e., localhost. I have selected the default database initially, but we can change that later in the command line. The port by default is 5432, and I have selected the username as the default user. Enter the password you have created when installing PostgreSQL setup. When we input the password, the below message will appear, and you will be entered in the database you have selected, i.e., “Postgres”.
Now that we are connected to a “Postgres” server, we will enter a specific database where we want to show the tables in a database. For that, run the following command to connect with the specific database we have created:
Note, you can input your choice of database name instead of “Testdb”. For example, “\c database name”.
We are entered into our database; now we want to find how many tables do “Testdb” database contains; run the following command to show tables in the “Testdb” database:
The above command will display all the tables the “Testdb” database contains as:
We can see that it shows all the tables in “Testdb” and the ones we created for examples in previous articles.
If you want the size and description of that table, you can run the below command to attain more information about the table:
The above command gives the following result, which includes the size and description column as shown:
The command “\dt+” will show the tables with all the information, including tables name, type, owner, persistence, access method, size, and description.
Show Tables Using pgAdmin4 in PostgreSQL:
The other method to show tables in PostgreSQL is by using pgAdmin4. In PostgreSQL, click on the specific database you want to view tables, then open the Query tool for showing tables that exist in a database. You can simply use a “Select” statement to view the “Testdb” database tables. Run the following query to show tables in PostgreSQL using pgAdmin4:
WHERE
schemaname != 'pg_temp_4'
AND schemaname!= 'pg_catalog'
AND schemaname!= 'information_schema';
Here, the “pg_tables” is used to retrieve the information from the tables we are looking for in a database. The “Where” clause is filtering the condition we have set for show tables. The “schemaname != ‘pg_temp_4’ “ states that don’t select the “schemaname” in the table where it is equal to “pg_temp_4”. The “And” condition returns the values when both the conditions on its right and left are determined as true.
The overall query will run like, first select from “pg_catalog.pg_tables” and don’t include the “schemaname” in the table as “pg_temp_4”, “pg_catalog”, and “information_schema”. The above query will give the following results:
All the tables are displayed in the output using pgAdmin4 as they were using SQL shell (psql). Most of the results are filtered out because of the “Where” clause condition.
If you want to display all the results, avoid using the “Where” clause in your statement.
You can also change the condition according to what you want to display in the output. For example, I want to display only the tables with “schemaname” is equal to “pg_temp_4”, run this query:
WHERE
schemaname = 'pg_temp_4';
This will select only the “pg_temp_4” from the table in the “Testdb” database, which will show this output on the screen:
All the tables with “schemaname” “pg_temp_4” are shown in the above output results.
Conclusion:
In this guide, we found the techniques to show tables in PostgreSQL using two different methods. First, we discussed how to show tables using the SQL shell (psql) tool; we entered in the “Testdb” database in which we used the “\dt” command to show the tables in that database. For detailed information, we used the “\dt+” command to get the size and description of the tables in “Testdb”. Secondly, we used pgAdmin4 to show tables in PostgreSQL. You can use the “Select” statement to show tables in PostgreSQL with the “Where” clause. The “Where” clause is used to specify a condition for displaying specific tables; if you want to display all tables in your output, then there is no need to use the “Where” clause. Both the methods we used in this article are efficient and easy to use, but the first method of showing tables using SQL shell (psql) is time-saving because you need to write a smaller command. The second method is scalable comparatively, as you can specify the conditions as well in the query tool to show filtered tables.
