【使用SQL语句输出PG数据库的数据字典】_pgsql 导出表数据结构字典sql语句

SELECT DISTINCT
	d.nspname AS "SchemaName",
	C.relname AS "表名",
	A.attnum AS "序号",
	A.attname AS "字段名称",
	format_type ( A.atttypid, A.atttypmod ) AS "字段类型",
	( CASE WHEN A.attnotnull = TRUE THEN 'NOT NULL' ELSE'' END ) AS "是否非空",
	( CASE WHEN b.description is not NULL THEN b.description ELSE'' END ) AS "备注"
FROM
	pg_class C,
	pg_attribute
	A LEFT OUTER JOIN pg_description b ON A.attrelid = b.objoid 
	AND A.attnum = b.objsubid,
	pg_type T,
	pg_namespace d
	LEFT OUTER JOIN information_schema.schemata f ON f.SCHEMA_NAME = d.nspname 
	AND f.schema_owner = 'bct',
	pg_tables e 
WHERE
	A.attnum > 0 
	AND A.attrelid = C.oid 
	AND A.atttypid = T.oid 
	AND e.tablename = C.relname 
	AND d.nspname = e.schemaname 
    AND e.schemaname != 'information_schema'
	AND e.schemaname != 'pg_catalog'
ORDER BY
	d.nspname,
	C.relname,
	A.attnum
 
-- 更改f.schema_owner即可用于不同的用户

PG数据库通用，只需要修改不同的用户，然后在【某个数据库】下进行执行即可。