How to list all indexes in PostgreSQL database
Sep 15th, 2021,Looking for a way to list indexes from the PostgreSQL database. You can do this by using one of the methods in this article.
1. Using SQL Query
You will be able to list all indexes from the PostgreSQL database by querying pg_indexes. The following SQL script will help you get all indexes from your database:
SELECT schemaname, indexname, tablename FROM pg_indexes WHERE schemaname = 'public' ORDER BY indexname;
Sample results
The output will be like this:
If you are looking to list all indexes of a specific table the SQL query to run is as follows.
SELECT schemaname, indexname, tablename FROM pg_indexes WHERE tablename = table_name ORDER BY indexname;
Sample results
Here is the output to list all indexes for the film table:
2. Using psql command-line
If you are using psql and you want to get a list of all indexes in the PostgreSQL database you can use the following command line \di
To list all indexes for a specific table using psql, you can use\d table_name
The command result for the table actor:
3. With ERBuilder Data Modeler
ERBuilder Data Modeler allows you to get all details of each table in your PostgreSQL database from columns, constraints, keys, triggers, SQL script, to the list of all indexes.
Furthermore, with ERBuilder Data Modeler you will have full control over the indexes of a table where you can edit existing ones, create new indexes or delete indexes, just by double-clicking on indexes from the indexes list.
How ERBuilder Helps
ERBuilder Data Modeler is a GUI data modeling tool that allows you to visualize, design and model databases by using entity relationship diagrams and automatically generates the most popular SQL databases including Amazon Redshift. Generate and share the data Model documentation with your team. Optimize your data model by using advanced features such as test data generation, schema compare and schema synchronization. Try now ERBuilder for 15 days FREE
Subscribe To Our Newsletter
Subscribe to our email newsletter today to receive updates of the latest news, tutorials and special offers!
