[SQL] Query to retrieve all indexed columns

[Bart van Houdt]

Hi,

 

I am working on a query to retrieve all indexed columns and came up with
the following query:

 

select pgc.relname as indexname

      ,pgc2.relname as tablename

      ,pga.attname as columnname

      ,pga.attnum as columnnumber

      ,replace(pgi.indkey::text, ' ', ',') as columnindex

  from pg_class pgc

  join pg_namespace pgn ON (pgn.oid = pgc.relnamespace

                            AND pgn.nspname = 'public')

  left join pg_index pgi ON (pgi.indexrelid = pgc.oid)

  left join pg_class pgc2 ON (pgc2.oid = pgi.indrelid)

  left join pg_attribute pga ON (pga.attrelid = pgc2.oid

                                 AND attnum::text IN
(replace(pgi.indkey::text, ' ', ',')))

where pgc.relkind = 'i'

order by indexname, columnindex;

 

Tis query works for single column indexes, but with multiple column
indexes I get incorrect results... I'm having a hard time figuring out
how to join pg_attribute.indkey in this, could anyone help me out on
this one?  

 

Thanks in advance,

 

Bart van Houdt

Syfact International B.V.

Database developer