unless’s blog

日々のちょっとした技術的なことの羅列

PostgreSQLでIndex名,column名,型名の一覧を取得するSQL

SQL PostgreSQL エンジニア

これはなに?

タイトルのままだけど、PostgreSQLでIndex名とcolumn名と型名の一覧が欲しかった しかし意外と取得に苦労したのでSQLを貼っておく

SQL

最初は Information_schema でうまいこと引いてこれないかなって思って試したけど意外とできなかった
理由としては Information_schema にcolumnとindexを紐付けるテーブルが存在しなかったこと Information_schema.colums でcolumnの情報は取れて、 pg_indexes でindexの情報は取れたけど紐付けは難しかった
pg_indexes のcreate文をparseすればいけるのかもしれないが、ちょっとだるいのでサクッとSQLだけで取りたかった

それと型の情報を出すのも意外にしんどい
PKだけであれば information_schema. table_constraints から引けば良さそうであったが、PK意外のindexが取れなそうであった

上記を踏まえて、システムカタログから取ってくるようにしてみた

www.postgresql.jp

SELECT
     t.relname AS table_name
    ,i.relname AS index_name
    ,array_to_string( array_agg( a.attname ), ',') AS column_names
    ,array_to_string( array_agg( tp.typname ), ',') AS column_types
FROM
     pg_class AS t
    ,pg_class AS i
    ,pg_index AS ix
    ,pg_attribute AS a
    ,pg_tables AS ta
    ,pg_type as tp
WHERE
        t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND ix.indisprimary = false
    AND ix.indisunique = false
    AND a.attrelid = t.oid
    AND a.attnum = ANY( ix.indkey )
    AND t.relkind = 'r'
    AND t.relname = ta.tablename
    AND ta.schemaname = current_schema()
    AND a.atttypid = tp.oid
GROUP BY
     t.relname
    ,i.relname
    ,ix.indisprimary
    ,ix.indisunique
ORDER BY
     t.relname
    ,i.relname;

複雑なSQLになってしまったが、取れたので良かろう

もっときれいに取れる方法知ってる人は教えてくだされ

関連Link