PostgreSQLでテーブル定義を確認したい！

「テーブル定義はあるけど、全然最新化されてねぇじゃねぇか！！」
ってこと、よくあるよね...
そんなときにSQL一発で全部のテーブル定義を抽出できるSQLをつくってみた。
※ただしPostgreSQLにかぎる

# テーブル定義の確認
## 実際のSQL
テーブル名、列名のコメントと一緒に取ってくるようにしている。
もし作成時にコメントをつけていなかったら、pg_descriptionとinner joinしているところを変更すれば、
物理名だけで取ってこれるはず。

2020/11/19:追記
PKの情報も一緒に持ってくるようにしてみた。

```sql
WITH table_define AS(
    SELECT
        isc.ordinal_position,
        isc.table_name,
        table_pgd.description,
        isc.column_name,
        pgd.description,
        CASE
            WHEN ccu.column_name = isc.column_name THEN '○'
            ELSE NULL
        END AS is_pk,
        isc.data_type,
        isc.character_maximum_length,
        isc.column_default,
        isc.is_nullable
    FROM
        information_schema.columns isc
        INNER JOIN
            pg_stat_user_tables psut
        ON  psut.relname = isc.table_name
        AND psut.schemaname = 'スキーマ名'
        INNER JOIN
            pg_description pgd
        ON  pgd.objoid = psut.relid
        AND pgd.objsubid = isc.ordinal_position
        INNER JOIN
            information_schema.table_constraints tc
        ON  tc.table_name = isc.table_name
        AND tc.table_schema = 'スキーマ名'
        AND tc.constraint_type = 'PRIMARY KEY'
        LEFT OUTER JOIN
            information_schema.constraint_column_usage ccu
        ON  ccu.table_catalog = tc.table_catalog
        AND ccu.table_schema = tc.table_schema
        AND ccu.table_name = ccu.table_name
        AND ccu.constraint_name = tc.constraint_name
        INNER JOIN
            (
                SELECT
                    *
                FROM
                    pg_description pgd
                WHERE
                    pgd.objsubid = 0
            ) table_pgd
        ON  table_pgd.objoid = psut.relid
    WHERE
    	isc.table_schema = 'スキーマ名'
)
SELECT
    *
FROM
    table_define td
ORDER BY
    td.table_name,
    td.is_pk NULLS LAST,
    td.ordinal_position
```

## ちょっとだけ説明
・information_schema とはデータベース内の様々メタデータを取得するために標準SQLで定められているビューの集合
・pg_descriptionには、コメントが格納されている
　「objoid」でテーブルを特定し、「objsubid」でカラムを特定する。
　「information_schema.columns」から、「objsubid」は取得できる。
・テーブルは「pg_stat_user_tables」から「objoid」を取得することでコメントの取得が可能。

# UNIQUEインデックスを一覧で取得するSQL
業務で必要になって使ったので追記。

```sql
select
    a2.nspname as namespase,
    a4.relname as table_name,
    a1.relname as index_name,
    a5.attname as column_name,
    position(cast(a5.attnum as text) in replace(cast(a3.indkey as text), ' ', '')) as index_position
--インデックスの定義順特定
from
    pg_class a1,
    pg_namespace a2,
    pg_index a3,
    pg_class a4,
    pg_attribute a5
where
    a1.relkind = 'i'
--インデックスのみ取得
and a1.relnamespace = a2.oid
--スキーマ（namespase）特定用
and a1.oid = a3.indexrelid
--インデックスの情報取得
and a3.indrelid = a4.oid
--インデックスが登録されているテーブルの名前特定用
and a5.attnum = any(a3.indkey)
-- インデックスのカラム特定
and a5.attrelid = a3.indrelid
and a3.indisunique = true
and a3.indisprimary = false
and a2.nspname = 'namespace'
order by
    namespase,
    table_name,
    index_name,
    index_position
    -- インデックスの定義順でソート
;
```

# リンク
- [Postgresでテーブルのカラム一覧をコメント付きで取得するSQL](https://ufirst.jp/memo/2014/11/30/postgres%E3%81%A7%E3%83%86%E3%83%BC%E3%83%96%E3%83%AB%E3%81%AE%E3%82%AB%E3%83%A9%E3%83%A0%E4%B8%80%E8%A6%A7%E3%82%92%E3%82%B3%E3%83%A1%E3%83%B3%E3%83%88%E4%BB%98%E3%81%8D%E3%81%A7%E5%8F%96%E5%BE%97/)
- [PostgreSQLにてテーブルやカラムの各種情報を取得するSQL (テーブル一覧, カラム一覧, プライマリーキー情報取得, テーブルのコメントを取得, カラムのコメントを取得)](https://devlights.hatenablog.com/entry/20080226/p1)
- [PostgreSQLでテーブル名カラム名を取得する方法](http://chopl.in/post/2013/11/07/how_to_retrieve_tables_and_columns_with_postgres/)
- [PostgreSQLのテーブルのカラム情報などを取得する](https://exiz.org/2013110716431)
- [PostgreSQLにおけるUNIQUEとUNIQUE INDEX](https://qiita.com/ytsugejad/items/96e6d7be540e661f4ba4)