August 2, 2023
Summary: in this tutorial, you will learn how to list indexes from a PostgreSQL database by using either
pg_indexes view or
PostgreSQL does not provide a command like
SHOW INDEXES to list the index information of a table or database.
However, it does provide you with access to the
pg_indexes view so that you can query the index information.
If you use the psql program to interact with the PostgreSQL database, you can use the
\d command to view the index information for a table.
PostgreSQL List Indexes using pg_indexes View
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 the name of the table to which the index belongs.
indexname: stores the name of the index.
tablespace: stores the name of the tablespace that contains indexes.
indexdef: stores index definition command in the form of
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;
The partial output is:
To show all the indexes of a table, you use the following statement:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'table_name';
For example, to list all the indexes for the
customer table, you use the following statement:
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'customer';
Here is the output:
If you want to get a list of indexes for tables whose names start with the letter
c, you can use the following query:
SELECT tablename, indexname, indexdef FROM pg_indexes WHERE tablename LIKE 'c%' ORDER BY tablename, indexname;
The following shows the output:
PostgreSQL List Indexes using psql command
If you use
psql to connect to a PostgreSQL database and want to list all indexes of a table, you can use the
\d psql command as follows:
The command will return all information about the table including the table’s structure, indexes, constraints, and triggers.
For example, the following statement returns detailed information about the
The output is:
As shown clearly in the output, you can find the index of the table under the indexes section.
In this tutorial, you have learned how to list all indexes from the PostgreSQL database by querying against the