PostgreSQL – List Indexes
In PostgreSQL, we use the pr_indexes view to list the indexes of a database. PostgreSQL does not provide a command like SHOW INDEXES to list the index information of a table or database. If you use psql to access the PostgreSQL database, you can use the \d command to view the index information for a table.
Using pg_indexes view
The pg_indexes view allows you to access useful information on each index in the PostgreSQL database. The pg_indexes view consists of five columns:
- schemaname: stores the name of the schema that contains tables and indexes.
- tablename: stores name of the table to which the index belongs.
- indexname: stores name of the index.
- tablespace: stores name of the tablespace that contains indexes.
- indexdef: stores index definition command in the form of CREATE INDEX statement.
Example 1:
The following statement lists all indexes of the schema public in the current database:
SELECT tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename, indexname;
Output:
Example 2:
The following statement lists all the indexes for the customer table, you use the following statement:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'customer';
Output:
Using psql command
The below syntax is used to list all the indexes of a table using psql command:
Syntax: \d table_name;
Example 1:
Here we will list all the indexes of the customer table of the sample database as shown below:
\d customer;
Output:
Example 2:
Here we will list all the indexes of the film table of the sample database as shown below:
\d film;
Output: