List all index names, column names and its table name of a PostgreSQL database

user_771245 0
Viewed 74038

What is the query to get the list all index names, its column name and its table name of a postgresql database?

I have tried to get the list of all indexes in a db by using this query but how to get the list of indexes, its column names and its table names?

 SELECT *
 FROM pg_class, pg_index
 WHERE pg_class.oid = pg_index.indexrelid
 AND pg_class.oid IN (
     SELECT indexrelid
     FROM pg_index, pg_class
     WHERE pg_class.oid=pg_index.indrelid
     AND indisunique != 't'
     AND indisprimary != 't'
     AND relname !~ '^pg_');
postgresql
6 Answers

For Non-Composite Indexes

select  t.relname,i.relname ,
     STRING_AGG(pga.attname||'', ','order by i.relname,pga.attnum)   as columnName          
    from pg_class t inner join pg_index ix
              on t.oid = ix.indrelid
              inner join  pg_class i
              on i.oid = ix.indexrelid
               inner join  pg_attribute pga
              on
               pga.attrelid = i.oid 
            inner join pg_indexes pgidx
                on pgidx.indexname=i.relname
             where 
               t.relkind = 'r' 
            and pgidx.schemaname='asit_cm'
             and t.relname ='accessory'
             group by  t.relname,i.relname having count(*)=1

For Composite Indexes

select  t.relname,i.relname ,
     STRING_AGG(pga.attname||'', ','order by i.relname,pga.attnum)   as columnName          
    from pg_class t inner join pg_index ix
              on t.oid = ix.indrelid
              inner join  pg_class i
              on i.oid = ix.indexrelid
               inner join  pg_attribute pga
              on
               pga.attrelid = i.oid 
            inner join pg_indexes pgidx
                on pgidx.indexname=i.relname
             where 
               t.relkind = 'r' 
            and pgidx.schemaname='asit_cm'
             and t.relname ='accessory'
             group by  t.relname,i.relname having count(*)=1
user_43143070
Related