概要
PostgreSQLでテーブルに設定されたインデックスの一覧を表示するSQLです。
インデックスを貼った列の順番ごとに表示します。
SQL
select
class_tbl.relname as table_name,
class_idx.relname as index_name,
idx.indisprimary as is_primary,
att0.attname as col0,
att1.attname as col1,
att2.attname as col2,
att3.attname as col3,
att4.attname as col4
from pg_index idx
inner join pg_class class_idx
on idx.indexrelid = class_idx.oid
inner join pg_class class_tbl
on idx.indrelid = class_tbl.oid
left join pg_attribute att0
on class_tbl.oid = att0.attrelid
and idx.indkey[0] = att0.attnum
left join pg_attribute att1
on class_tbl.oid = att1.attrelid
and idx.indkey[1] = att1.attnum
left join pg_attribute att2
on class_tbl.oid = att2.attrelid
and idx.indkey[2] = att2.attnum
left join pg_attribute att3
on class_tbl.oid = att3.attrelid
and idx.indkey[3] = att3.attnum
left join pg_attribute att4
on class_tbl.oid = att4.attrelid
and idx.indkey[4] = att4.attnum
where 1=1
--and table_name = 'テーブル名'
