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:
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:
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:
In the below example, the following command is used to get the complete information about the employee table:
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:
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:
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:
After executing the above command, we will get the following output, which displays those tables whose name start with the letter e:
In the PostgreSQL List Indexes section, we have learned the following topics: