Introduction to PostgreSQL Show Tables
We often need to study and observe the complete data structures and the objects stored in the database that we are using. Knowing beforehand the tables that are present in our current database not only helps us to understand our database clearly but also lets us know the names of the tables and the properties of them before using them. In Postgres, we can show all the tables that are present in a particular database by using either of the two methods that are available in PostgreSQL. One of the most efficient and easy ways of doing so is by using the facility provided in Postgres names psql utility which provides various metacommands that are short and easy to use.
To get the tables present in the database, we can make the use of psql meta-command named \dt that lists out all the tables of the connected database. Another way of doing so is to use the pg_tables table that is present in pg_catalog schema that stores the details of all the tables of the database. In this article, we will make the use of both these methods to list out the tables that are present in a particular database with the help of an example.
Psql metacommand to Retrieve the Tables
The metacommands are the psql utility provided in PostgreSQL which provides us with a set of stored commands that can be accessed by simply prepending the \ backslash before the alphabetic letters that signify certain command that is followed by different operators on the optional basis which enhance the operation. To retrieve the tables present in the database and show them in the list format, we can make the use of the \dt meta-command that is provided in PostgreSQL.
We will first need to login to our PostgreSQL database server through the terminal using the following command where Postgres is the user name using which I will log in to my Postgres database server and W is to mention that password will be entered by me after the execution of the following command.
Code:
psql -U postgres -W
The execution of the above command statement gives an output which is as shown below which demands the password as soon as the command is fired and we need to mention the password that is set against the user using which we are trying to login. For example, here the user is Postgres whose password is “a”.
Output:
Further, after connecting to the database server, we will need to list out all the available databases that are present in the database server. So that we can choose the one and connect to it and retrieve the tables of that database. To get the list of all the database names, we can make the use of meta-command named \d as shown below.
Code:
\d
Output:
Now, we will connect to the educba database using the meta-command \c and name of the database. We can make the use of the alternative command named \connect that helps us to connect to the database.
We will use the metacommand \c as shown below to connect to educba database.
Code:
\c educba
Output:
Now, to show all the tables that are store inside the educba database, we can make the use of meta-command \dt.
We can simply execute the following command.
Code:
\dt
Output:
We can observe that there are two tables namely demo and educba_demo that are present in the Postgres database and are public. To get the additional details about the tables that are retrieved, we can make the use of the same meta-command and append the + plus operator to it as shown below.
Code:
\dt+
Output:
We can observe that besides the name of the table, its owner, and schema type we can get the size of the table and the additional description if set against the table.
Using the pg_tables to Get the List of Tables
We can also make the use of the alternative PostgreSQL query on the table pg_tables that is located inside the pg_catalog database that stores the details of all the tables that include all the user-defined tables and the system tables present in PostgreSQL database. The system tables also called schemas named information_schema and pg_catalog are already present in our PostgreSQL once it is installed. Hence, we will need to retrieve the records of the pg_tables table except for the records whose schema names are information_schema and pg_catalog. Finally, we can execute the following query to show the tables in PostgreSQL.
Code:
SELECT *
FROM pg_catalog.pg_tables
WHERE schemaname != 'information_schema'
AND schemaname != 'pg_catalog' ;
Output:
We can observe the details about whether the tables have the indexes on it, rules, triggers, and security associated with it from the above query.
There is one more way, using which we can retrieve the list of the tables of the particular database. We can make the use of the following query statement using the following query as that tables table inside the information_schema stores the details of the tables of the database.
Code:
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;
Output:
We can observe three records are retrieved instead of two as this query retrieves the views along with tables and remote_educba_data is the view present in the educba database.
Conclusion – PostgreSQL Show Tables
We can show all the tables of a particular database in the PostgreSQL using either of the three ways that include the metacommands of psql utility, using the pg_tables table of pg_catalog, and using the tables table of information_schema.
Recommended Articles
This is a guide to PostgreSQL Show Tables. Here we discuss the introduction, Psql metacommand to retrieve the tables, using the pg_tables to get the list of tables. You may also have a look at the following articles to learn more –
