Javatpoint Logo
Javatpoint Logo

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:

Output

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

PostgreSQL List Indexes

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

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:

Output

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

PostgreSQL List Indexes

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.

PostgreSQL List Indexes

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:

PostgreSQL List Indexes

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.





Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA