postgreSQLでテーブルとカラムの一覧をコメント付きで出力する

システムをリプレイスするにあたって、既存のテーブル一覧が欲しかったので出してみた
と言っても自分で考えられなかったので、他の方のページを参考にして、
組み合わせて自分の欲しい形に整形しただけです。

クエリ

SELECT
    relname as "テーブル名",
    pd.description as "テーブルコメント",
    colname as "列名",
    tbl.description as "コメント",
    CASE
        WHEN size IS NULL
            THEN datatype
        -- サイズ情報が取得できた場合は付加
        ELSE datatype || ' (' || size || ')'
    END AS "データ型",
    CASE
        -- NOT NULL情報 と DEFAULT情報をマージ
        WHEN "notnull" IS NOT NULL AND "default" IS NOT NULL
            THEN "notnull" || ' ' || "default" -- 
        ELSE COALESCE("notnull", "default")
    END AS "属性"
FROM
    (
        SELECT
            cls.oid,
        cls.relname,
            attr.attnum AS idx, -- 列番号
            attr.attname AS colname, -- 列名
            CASE typ.typname -- 分かりやすい名前に変更
                WHEN 'int2'   THEN 'SMALLINT'
                WHEN 'int4'   THEN 'INT'
                WHEN 'int8'   THEN 'BIGINT'
                WHEN 'float4' THEN 'REAL'
                WHEN 'float8' THEN 'DOUBLE'
                WHEN 'bpchar' THEN 'CHAR'
                ELSE UPPER(typ.typname)
            END AS datatype, -- データ型
            -- attr.atttypmod が正の整数なら、
            -- サイズ付加情報あり？
            -- （よく調べて無いので自信なし）
            CASE WHEN attr.atttypmod > 0 THEN
                CASE typ.typname
                    WHEN 'numeric'
                        THEN (attr.atttypmod - 4) / 65536
                    WHEN 'decimal'
                        THEN (attr.atttypmod - 4) / 65536
                    WHEN 'date'
                        THEN COALESCE(attr.atttypmod - 4, 10)
                    WHEN 'time'
                        THEN COALESCE(attr.atttypmod - 4, 8)
                    WHEN 'timestamp'
                        THEN COALESCE(attr.atttypmod - 4, 19)
                    ELSE attr.atttypmod - 4
                END
            END AS size,
            -- attnotnull が true なら NOT NULL
            CASE attr.attnotnull
                WHEN TRUE THEN 'NOT NULL'
                ELSE NULL
            END AS notnull,
            -- atthasdef が true なら DEFAULT 値あり
            CASE attr.atthasdef
                WHEN TRUE THEN 'DEFAULT ' || adef.adsrc
                ELSE NULL
            END AS default,
            pd.description
        FROM
            pg_class cls
                INNER JOIN pg_attribute attr
                    ON (cls.oid = attr.attrelid)
                INNER JOIN pg_type typ
                    ON (attr.atttypid = typ.oid)
                LEFT JOIN pg_description pd
                    ON (pd.objoid = cls.oid
                    and pd.objoid = attr.attrelid
            and pd.objsubid = attr.attnum
            )
                -- デフォルト値情報
                LEFT OUTER JOIN pg_attrdef adef
                    ON (cls.oid = adef.adrelid AND attr.attnum = adef.adnum)
        WHERE
            cls.oid in (
        SELECT
            cls.oid
        FROM
            pg_class cls
            INNER JOIN pg_namespace nsp
                ON (cls.relnamespace = nsp.oid)
            INNER JOIN pg_user usr
                ON (cls.relowner = usr.usesysid)
        WHERE
            cls.relkind = 'r'
             AND nsp.nspname = 'public' -- スキーマ名で絞込み
        ORDER BY
            nsp.nspname,
            usr.usename,
            cls.relname
                ) -- pg_class.oid を入れよ
            -- 列番号が0以下の場合、システム列なので対象にしない
            AND attr.attnum >= 0
            -- attisdropped が TRUE なら、
            -- この列はすでに削除されていて無効状態
            AND attr.attisdropped IS NOT TRUE
            AND typ.typisdefined
    ) AS tbl
left join pg_description pd
on tbl.oid = pd.objoid and pd.objsubid=0
ORDER BY oid, idx;

出力結果

テーブル名	テーブルコメント	列名	コメント	データ型	属性
sections	部署	id		INT	NOT NULL DEFAULT nextval('sections_id_seq'::regclass)
sections	部署	code	部署コード	VARCHAR (255)
sections	部署	name	部署名	VARCHAR (255)
sections	部署	created_at		TIMESTAMP
sections	部署	updated_at		TIMESTAMP
sections	部署	deleted		SMALLINT	NOT NULL DEFAULT 0
members	メンバー	id		INT	NOT NULL DEFAULT nextval('members_id_seq'::regclass)
members	メンバー	section_id	部署ID	INT	NOT NULL
members	メンバー	code	コード	INT	NOT NULL
members	メンバー	name	名前	INT	NOT NULL
members	メンバー	created_at	作成日時	TIMESTAMP
members	メンバー	updated_at	更新日時	TIMESTAMP

これをエクセルに貼って、新旧データの比較を作っておけばデータ移行もスムーズにいけるかも

参考

PostgreSQLにてテーブルやカラムの各種情報を取得するSQL (テーブル一覧, カラム一覧, プライマリーキー情報取得, テーブルのコメントを取得, カラムのコメントを取得)

DB内のテーブル名一覧、テーブル情報などを取得するSQL