Open In App

PostgreSQL – List Indexes

Improve
Improve
Improve
Like Article
Like
Save Article
Save
Share
Report issue
Report

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
Previous
Next
Share your thoughts in the comments
Similar Reads