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 (テーブル一覧, カラム一覧, プライマリーキー情報取得, テーブルのコメントを取得, カラムのコメントを取得)](http://devlights.hatenablog.com/entry/20080226/p1)

[DB内のテーブル名一覧、テーブル情報などを取得するSQL](http://d.hatena.ne.jp/ja9/20100830/1283146058)