Query to retrieve all indexed columns - Mailing list pgsql-sql

From Bart van Houdt
Subject Query to retrieve all indexed columns
Date
Msg-id CECE69D480C32F49891F27ED3E49C38C04A703EF@nthvsexch02.interaccess.nl
Whole thread Raw
Responses Re: Query to retrieve all indexed columns
List pgsql-sql
<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>

pgsql-sql by date:

Previous
From: mahmoud ewiwi
Date:
Subject: Re: custom serial number
Next
From: "A. Kretschmer"
Date:
Subject: Re: custom serial number