PostgreSQLで情報スキーマの利用

SELECT * FROM information_schema.tables WHERE table_schema = 'public';

SELECT * FROM information_schema.enabled_roles;

SELECT * FROM information_schema.table_constraints WHERE table_schema = 'public';

または
SELECT table_name, constraint_type, constraint_name FROM information_schema.table_constraints WHERE table_schema = 'public';

または
SELECT constraint_name, check_clause FROM information_schema.check_constraints WHERE constraint_schema = 'public';

SELECT table_name, column_name, column_default, is_nullable, data_type, character_maximum_length FROM information_schema.columns WHERE table_schema = 'public';

（columnテーブルの列を確認したければ次のコマンド）
SELECT column_name FROM information_schema.columns WHERE table_name = 'columns' AND table_schema = 'information_schema';

SELECT table_name, count(column_name) AS column_amount FROM information_schema.columns WHERE table_schema = 'public' GROUP BY table_name;

SELECT table_name, count(column_name) AS column_amount FROM information_schema.columns WHERE table_schema = 'public' GROUP BY table_name ORDER BY column_amount DESC LIMIT 1;

SELECT table_name, count(constraint_type) FROM information_schema.table_constraints WHERE constraint_schema = 'public' AND constraint_type = 'FOREIGN KEY' GROUP BY table_name;

（各テーブルの主キーも表示される。気にするなら上のinformation_schema.table_constraintsテーブルを結合すれば良いはず）

SELECT
    key.table_name,
    key.column_name,
    key.constraint_name,
    usage.table_name,
    usage.column_name
FROM
        information_schema.key_column_usage AS key
            INNER JOIN
            information_schema.constraint_column_usage AS usage
ON key.constraint_name = usage.constraint_name
WHERE
    key.table_schema = 'public';

SELECT
    c.table_name, c.column_name, c.column_default
FROM
    information_schema.columns AS c
WHERE
    c.table_schema = 'スキーマ名'
    AND c.table_name = 'テーブル名'
    AND c.table_catalog = 'DB名'
    AND c.column_default LIKE '%regclass%';