PostgreSQL Show Tables

Updated May 24, 2023

Introduction to PostgreSQL Show Tables

We often need to study and observe the complete data structures and the objects stored in our database. Knowing beforehand the tables that are present in our current database not only helps us understand our database clearly but also lets us know the names of the tables and their properties before using them. In Postgres, we can show all the tables in a particular database by using either of the two methods 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 use the 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 in the pg_catalog schema that stores the details of all the database tables. In this article, we will use both these methods to list the tables 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 use the \dt meta-command 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 I will enter that password after the execution of the following command.

Code:

psql -U postgres -W

The execution of the above command statement gives an output, as shown below, which demands the password as soon as the command is fired, and we need to mention the password 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 all the available databases in the database server so that we can choose the one and connect to it and retrieve the tables of that database. We can use a meta-command named \d, as shown below, to get the database names list.

Code:

\d

Output:

Now, we will connect to the educba database using the meta-command \c and the name of the database. We can use the alternative command named \connect that helps us connect to the database.

We will use the metacommand \c as shown below to connect to the educba database.

Code:

\c educba

Output:

Now, to show all the tables stored inside the educba database, we can use meta-command \dt.

We can execute the following command.

Code:

\dt

Output:

We can observe that two tables, namely demo and educba_demo, are present in the Postgres database and are public. To get the additional details about the retrieved tables, we can use 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 use of the alternative PostgreSQL query on the table pg_tables, which 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 the PostgreSQL database. The system tables, also called schemas named information_schema and pg_catalog, are already present in our PostgreSQL once 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:

From the above query, we can observe the details about whether the tables have the indexes on them, rules, triggers, and security associated with it.

There is one more way to retrieve the list of the tables of the particular database. We can use 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 that three records are retrieved instead of two as this query retrieves the views along with tables, and remote_educba_data is the view in the educba database.

Conclusion – PostgreSQL Show Tables

We can show all the tables of a particular database in PostgreSQL using either of the three ways that include the metacommands of the psql utility, using the pg_tables table of pg_catalog, and using the tables table of information_schema.

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Show Tables” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL IS NULL
2. Restore Database in PostgreSQL
3. PostgreSQL ORDER BY Random
4. PostgreSQL TO_DATE()