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:
Last Updated :
22 Jul, 2021
Like Article
Save Article
Share your thoughts in the comments
Please Login to comment...