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
