Search code examples
postgresqlschemametadata

How to query the metadata of indexes in PostgreSQL

I need to be able to query a PostgreSQL database to obtain information about the indexes present and their details.

On SQL Server, I can do the following to get a list of all tables/indexes/columns for all indexes:

select TABLE_NAME, INDEX_NAME, NON_UNIQUE, COLUMN_NAME
from INFORMATION_SCHEMA.STATISTICS
where TABLE_SCHEMA = 'my_schema'
order by TABLE_NAME, INDEX_NAME, SEQ_IN_INDEX

It seems that the STATISTICS table of INFORMATION_SCHEMA is a SQL Server extension. How can I do the equivalent in PostgreSQL?

EDIT: I'm specifically trying to return a denormalized result set as follows

TableName, IndexName, UniqueFl, ColumnName

So I get a row back for each column in all indexes.

Thanks, Jon

Solution

  • I don't think this is possible from the information_schema see this discussion. Indexes created other than from constraints won't be in the information schema.

    However from the system tables you can see this question