PostgreSQLでプライマリキー、ユニークインデックス、インデックスを取得する方法

PostgreSQL でテーブルに設定されている「プライマリキー、ユニークインデックス、インデックス」の一覧を取得する必要があるので、SQLを作ってみます。

プライマリキー、ユニークインデックス、インデックスを取得する

PostgreSQL でテーブルに設定されている「プライマリキー、ユニークインデックス、インデックス」の一覧を取得するにはいくつかのカタログを参照するので少し複雑ですが SQL で取得することが可能です。

PostgreSQLでのインデックスの種類

PostgreSQL で利用できるインデックスの種類は

1. プライマリキー
2. ユニークインデックス
3. インデックス

があります。

プライマリキーはテーブル内のレコードを一意に決めるためのカラム（列）の組み合わせです。
プライマリキーはテーブルに 1 つしか設定することはできません。

ユニークインデックスはプライマリーキーと同様にテーブル内のレコードを一意に決めるためのカラム（列）の組み合わせです。
ユニークインデックスはテーブルに複数設定することができます。

インデックスは行を検索する際に高速化を図るための列の組み合わせです。
インデックスを利用した SELECT 文はそうでないものよりより高速にデータを検索することが可能になります。
インデックスはテーブルに複数設定することができます。

プライマリキーの一覧を取得する

PostgreSQL でテーブルに設定されているプライマリキーを取得するには pg_index スキーマの indisprimary カラムに true が設定されている行を取得します。

実行する SQL は以下の通りです。

SELECT
     t.relname AS table_name
    ,i.relname AS index_name
    ,array_to_string( array_agg( a.attname ), ',') AS column_names
FROM
     pg_class AS t
    ,pg_class AS i
    ,pg_index AS ix
    ,pg_attribute AS a
    ,pg_tables AS ta
WHERE
        t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
	AND ix.indisprimary = true
    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()
GROUP BY
     t.relname
    ,i.relname
    ,ix.indisprimary
ORDER BY
     t.relname
    ,i.relname

上記の SQL を実行すると現在接続しているデータベースのスキーマに所属するテーブルのプライマリキーの一覧が取得できます。

ユニークインデックスの一覧を取得する

PostgreSQL でテーブルに設定されているユニークインデックスを取得するには pg_index スキーマの indisprimary カラムに false 、 indisunique カラムに true が設定されている行を取得します。

実行する SQL は以下の通りです。

SELECT
     t.relname AS table_name
    ,i.relname AS index_name
    ,array_to_string( array_agg( a.attname ), ',') AS column_names
FROM
     pg_class AS t
    ,pg_class AS i
    ,pg_index AS ix
    ,pg_attribute AS a
    ,pg_tables AS ta
WHERE
        t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
	AND ix.indisprimary = false
	AND ix.indisunique = true
    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()
GROUP BY
     t.relname
    ,i.relname
    ,ix.indisprimary
    ,ix.indisunique
ORDER BY
     t.relname
    ,i.relname

上記の SQL を実行すると現在接続しているデータベースのスキーマに所属するテーブルのユニークインデックスの一覧が取得できます。

インデックスの一覧を取得する

PostgreSQL でテーブルに設定されているインデックスを取得するには pg_index スキーマの indisprimary カラムに false 、 indisunique カラムに false が設定されている行を取得します。

実行する SQL は以下の通りです。

SELECT
     t.relname AS table_name
    ,i.relname AS index_name
    ,array_to_string( array_agg( a.attname ), ',') AS column_names
FROM
     pg_class AS t
    ,pg_class AS i
    ,pg_index AS ix
    ,pg_attribute AS a
    ,pg_tables AS ta
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()
GROUP BY
     t.relname
    ,i.relname
    ,ix.indisprimary
    ,ix.indisunique
ORDER BY
     t.relname
    ,i.relname

上記の SQL を実行すると現在接続しているデータベースのスキーマに所属するテーブルのインデックスの一覧が取得できます。

まとめ

PostgreSQL でテーブルに設定されているプライマリキー、ユニークインデックス、インデックスを取得するには pg_index スキーマの indisprimary カラムと indisunique カラムを参照してそれぞれ取得します。