PostgreSQL List Indexes

In this section, we are going to understand the working of the List Indexes from a PostgreSQL Database with the help of SQL Shell (PSQL) and pg_indexes view. And the examples of the Listing the indexes using psql and pg_indexes view.

What are PostgreSQL List Indexes?

In PostgreSQL, we do not have a command like SHOW INDEXES for listing the indexes data of a database or a table.

But the PostgreSQL allows to access to listing the indexes in two different ways, which are as follows:

• The SQL shell(psql): If we use psql to retrieve the PostgreSQL database, the \d command is used to view the index data for a table.
• The pg_indexes view: The pg_indexesview provides us to access useful information on each index in the PostgreSQL database.

We will now understand the process of using the psql and pg_indexes view one by one to list the indexes into the PostgreSQL database or a table.

PostgreSQL List Indexes using psql command

We are going to follow the below process to list a table in psql:

• Firstly, we will open the psqlin our local system, and we will connect to the database where we want to create a function.
• We will create a table in the w3cschoooldatabase, which we created earlier in the PostgreSQL tutorial.
• For connecting a database, we will enter the below command:

Output

After executing the above command, we will get the following output:

If we want to list all indexes of a table and to connect to a PostgreSQL database, we can use the below psql command:

The above command is used to return all information on the table with the table's structure, indexes, triggers, and constraints.

In the below example, the following command is used to get the complete information about the employee table:

Output

After executing the above command, we will get the following output, which displays the table's index under the indexes section.

PostgreSQL List Indexes using pg_indexes view

In PostgreSQL, the pg_indexes view allows us to get the important data of all the indexes in the PostgreSQL database.

The pg_indexes view contains five columns, which are as explained below:

In the below command, we will use all the five-column which we explained in the above table to list all indexes of the schema public in the existing database:

Output

After executing the above command, we will get the following output, which shows the complete list of tables created in the Organization database.

The below illustration is used to display all the indexes of a table:

In the following example, we are trying to retrieve the list of all the indexes for the Employee table, as shown in the below command:

Output

After implementing the above command, we will get the following output, which shows all the indexes for the employee table.

We can use the below command if we need to get a list of indexes for tables whose name start with the letter e:

Output

After executing the above command, we will get the following output, which displays those tables whose name start with the letter e:

Overview

In the PostgreSQL List Indexes section, we have learned the following topics:

• We have used the PostgreSQL List indexescommand to list all the indexes from the PostgreSQL database.
• We have understood the process of listing the indexes with different methods such as SQL shell(psql) and pg_indexes view.