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 javatpointdatabase, which we created earlier in the PostgreSQL tutorial.
• For connecting a database, we will enter the below command:

\c Organization   

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:

\d table_name  

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:

\d employee  

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:

Columns	Explanation
schemaname	The schemaname column is used to store the name of the schema, which includes the indexes and the tables.
tablename	The tablename column is used to keep the table name where the index belongs.
indexname	The name of the index stores in the indexname column.
tablespace	The tablespace column is used to keep the name of the tablespace, which involves the indexes.
indexdef	The indexdef column stores the index definition command in the form of the CREATE INDEX command.

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:

SELECT tablename, indexname, indexdef  

FROM pg_indexes  

WHERE schemaname = 'public'  

ORDER BY tablename, indexname;

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:

SELECT tablename, indexname, indexdef  

FROM pg_indexes  

WHERE schemaname = 'public'  

ORDER BY tablename, indexname;

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:

SELECT indexname, indexdef  

FROM pg_indexes  

WHERE tablename = 'employee';

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:

SELECT tablename, indexname, indexdef  

FROM pg_indexes  

WHERE tablename LIKE 'e%'  

ORDER BY tablename, indexname;

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.