<div class="Section1"><p class="MsoNormal">Hi,<p class="MsoNormal"> <p class="MsoNormal">I am working on a query to
retrieveall indexed columns and came up with the following query:<p class="MsoNormal"> <p class="MsoNormal">select
pgc.relnameas indexname<p class="MsoNormal"> ,pgc2.relname as tablename<p class="MsoNormal"> ,pga.attname as
columnname<pclass="MsoNormal"> ,pga.attnum as columnnumber<p class="MsoNormal"> ,replace(pgi.indkey::text, '
',',') as columnindex<p class="MsoNormal"> from pg_class pgc<p class="MsoNormal"> join pg_namespace pgn ON (pgn.oid =
pgc.relnamespace<pclass="MsoNormal"> AND pgn.nspname = 'public')<p class="MsoNormal"> left
joinpg_index pgi ON (pgi.indexrelid = pgc.oid)<p class="MsoNormal"> left join pg_class pgc2 ON (pgc2.oid =
pgi.indrelid)<pclass="MsoNormal"> left join pg_attribute pga ON (pga.attrelid = pgc2.oid<p
class="MsoNormal"> AND attnum::text IN (replace(pgi.indkey::text, ' ', ',')))<p
class="MsoNormal">wherepgc.relkind = 'i'<p class="MsoNormal">order by indexname, columnindex;<p class="MsoNormal"> <p
class="MsoNormal">Tisquery works for single column indexes, but with multiple column indexes I get incorrect results…
I’mhaving a hard time figuring out how to join pg_attribute.indkey in this, could anyone help me out on this one? <p
class="MsoNormal"> <pclass="MsoNormal">Thanks in advance,<p class="MsoNormal"> <p class="MsoNormal"><b><span lang="NL"
style="font-size:10.0pt;font-family:"Arial","sans-serif";
color:#1B5DA7">Bart van Houdt</span></b><p class="MsoNormal"><span lang="NL"
style="font-size:10.0pt;font-family:"Arial","sans-serif";
color:black">Syfact International B.V.</span><p class="MsoNormal"><span lang="NL"
style="font-size:10.0pt;font-family:"Arial","sans-serif";
color:black">Database developer</span></div>