1. 获取表中普通信息：如字段名，字段类型等等。
SELECT column_name, data_type, ordinal_position, is_nullable
FROM information_schema."columns"
WHERE "table_name"='TABLE-NAME' -- 将 'TABLE-NAME' 换成自己的表
获取所有的表和视图
SELECT table_name, table_type FROM INFORMATION_SCHEMA.tables WHERE table_schema='public' AND table_type IN ('BASE TABLE','VIEW')
获取约束注释
SELECT obj_description(oid, 'pg_constraint') AS d FROM pg_constraint WHERE conname = constraint_name;
2.需要获取如comment，主外键等
SELECT DISTINCT
a.attnum as num,
a.attname as name,
format_type(a.atttypid, a.atttypmod) as type,
a.attnotnull as notnull,
com.description as comment,
coalesce(i.indisprimary,false) as primary_key,
def.adsrc as default
FROM pg_attribute a
JOIN pg_class pgc ON pgc.oid = a.attrelid
LEFT JOIN pg_index i ON
(pgc.oid = i.indrelid AND i.indkey[0] = a.attnum)
LEFT JOIN pg_description com on
(pgc.oid = com.objoid AND a.attnum = com.objsubid)
LEFT JOIN pg_attrdef def ON
(a.attrelid = def.adrelid AND a.attnum = def.adnum)
WHERE a.attnum > 0 AND pgc.oid = a.attrelid
AND pg_table_is_visible(pgc.oid)
AND NOT a.attisdropped
AND pgc.relname = 'TABLE_NAME' -- 将 'TABLE-NAME' 换成自己的表
ORDER BY a.attnum;
