PostgreSQLでテーブルの列を取得するクエリ(2) / PostgreSQLでシーケンス一覧を取得するクエリ

PostgreSQLでテーブルの列を取得するクエリ(2)

プライマリキーかどうかも表示してみた。

SELECT
    att.attnum AS NUM,
    att.attname AS COL_NAME,
    typ.typname AS COL_TYPE,
    dsc.description AS COL_COMMENT,
    con.contype AS PRIMARY_KEY

FROM
    pg_class cls
    LEFT JOIN pg_attribute att ON cls.oid = att.attrelid
    LEFT JOIN pg_type typ ON att.atttypid = typ.oid
    LEFT JOIN pg_description dsc ON att.attrelid = dsc.objoid
                                    AND att.attnum = dsc.objsubid
    LEFT JOIN pg_constraint con ON con.conrelid = cls.oid AND att.attnum = ANY(con.conkey)
WHERE

    att.attnum > 0
AND cls.relname = 't_hogehoge'
AND (con.contype='p' OR con.contype IS NULL)

ORDER BY
    att.attnum

PostgreSQLでシーケンス一覧を取得するクエリ

SELECT
 cs.relname, ct.relname, a.attname
FROM
 pg_class cs
 JOIN pg_depend d ON cs.oid=d.objid
 JOIN pg_class ct ON ct.oid=d.refobjid
 JOIN pg_attribute a ON d.refobjid=a.attrelid AND d.refobjsubid=a.attnum
WHERE
 cs.relkind='S'