How to List All Tables in PostgreSQL INFORMATION_SCHEMA Table

1. Basic SELECT Query to Retrieve Tables Inside INFORMATION_SCHEMA in PostgreSQL
2. SELECT Query Modifications for INFORMATION_SCHEMA in PostgreSQL
3. the PSQL Statement for INFORMATION_SCHEMA Listing (Working Update) in PostgreSQL
4. the \z for Returning Tables in a Database in PostgreSQL
5. \z Modifications to Get All Tables in PostgreSQL

Let’s begin with a simple question. What is INFORMATION_SCHEMA exactly?

The INFORMATION_SCHEMA gives us information on the objects defined in our database. It contains a set of VIEWS, which are stored QUERY database objects.

The query below is like a VIEW as it returns a logical TABLE that contains the rows of YOUR_TABLE.

Select * from [your_table]

The INFORMATION_SCHEMA is pre-existent, meaning that the database user has access to this table and all the privileges, including DROP, when required.

This SCHEMA contains various database objects, so if you want to access some specific object, you are better off writing the object’s name, such as TABLES, to retrieve all tables.

Now let’s go ahead and see how we can use it.

Basic SELECT Query to Retrieve Tables Inside INFORMATION_SCHEMA in PostgreSQL

A very simple query to get all the tables inside this SCHEMA would be to write something like this.

select * from information_schema.tables

This would return a table like this.

Output:

Here you can see all the tables inside your database and their TYPE.

However, there’s a confusing aspect to this output. If you scroll down the OUTPUT a little, you’ll notice this.

Output:

Here you will see different tables with the TABLE_SCHEMA set to INFORMATION_SCHEMA. But, didn’t we call INFORMATION_SCHEMA.TABLES, then what is this?

Let us explain. The TABLE_SCHEMA tells us the SCHEMA contains the table.

When we call INFORMATION_SCHEMA.TABLES, it returns all the objects as defined by the database rules in their documentation. Hence, it also includes PG_CATALOG and PUBLIC tables.

But the tables with the TABLE_SCHEMA set to INFORMATION_SCHEMA tend to follow something called the SQL STANDARD, meaning that they can be viewed on other different DBMS systems. You may look into the SQL STANDARD by searching ISO/IEC 9075 on Google.

In comparison, PG_CATALOG has only PostgreSQL-specific tables; thus, they are included in this domain.

As a side note, INFORMATION_SCHEMA may tend to output these SYSTEM tables too as meta-data conforming with the SQL STANDARD.

SELECT Query Modifications for INFORMATION_SCHEMA in PostgreSQL

To get the INFORMATION_SCHEMA tables respectively from the range of tables returned by the query above, we use:

select * from information_schema.tables where table_schema = 'information_schema'

Or, if you want to display different tables sets, you may change the TABLE_SCHEMA to PUBLIC or PG_CATALOG.

Running the same query in PSQL returns:

Output:

the PSQL Statement for INFORMATION_SCHEMA Listing (Working Update) in PostgreSQL

To view INFORMATION_SCHEMA in the PSQL console, you may issue the statement:

postgres=# \dt information_schema.*

This will return all objects from the INFORMATION_SCHEMA. DT is used as a short form for listing tables.

the \z for Returning Tables in a Database in PostgreSQL

Another pretty common method used may include \z. This is mistakenly written as '/z', which may result in an error.

The statement is as follows.

postgres-# \z

This mostly returns the tables made by the user. Also, you may use:

postgres-# \dn information_schema

This tells us the OWNER of the SCHEMA.

\z Modifications to Get All Tables in PostgreSQL

An alternative to the first solution would be to use:

postgres-# \dt *.*

This will return all the tables as what’s done before.

We hope you learned the different ways in which we can display the INFORMATION_SCHEMA table for our users. We always try our best to explore possible ways to solve a problem.

We have covered the display of INFORMATION_SCHEMA today and different commands and tables that may be utilized.