【PostgreSQL】カラム名を検索するSQLのサンプル

ある文字列を含むカラムの一覧を検索したいときに使えるSQLのサンプルを紹介します。

カラム一覧のSQLサンプル

SELECT
    tables.relname as table_name,
    pa.attname as column_name
FROM
    pg_attribute as pa,
    (
    -- テーブル
    SELECT
        *
    FROM
        pg_stat_user_tables psut
    WHERE
        psut.relname like '%table_name%' -- テーブル名の条件を入力
    ORDER BY
        psut.relname
    ) as tables
WHERE
    pa.attnum > 0
    AND pa.attisdropped is FALSE
    AND pa.attname like '%column_name%'  -- カラム名の条件を入力
    AND tables.relid = pa.attrelid
ORDER BY
    pa.attname;

簡単にSQLの中身を説明すると以下のようになっています。
まず、サブクエリで、条件に該当するテーブルの一覧を取得しています。
テーブル一覧は、pg_stat_user_tablesというテーブルが情報を持っています。

そこで取得したテーブル一覧とカラム一覧を結合しています。
カラム一覧は、pg_attributeというテーブルが保持しています。
また、pg_attributeは削除されて現在存在しない列なども保存しているため、
pa.attnum > 0
AND pa.attisdropped is FALSE
といった条件句でそれらを除外しています。

結合条件は、「tables.relid = pa.attrelid」になります。
これは、条件によって抽出済みの pg_stat_user_tables.relid と pg_attribute.attrelid をキー結合しているという意味になります。