【使用SQL语句输出PG数据库的数据字典】

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数据库通用，只需要修改不同的用户，然后在【某个数据库】下进行执行即可。

最后

以上就是老实龙猫为你收集整理的【使用SQL语句输出PG数据库的数据字典】的全部内容，希望文章能够帮你解决【使用SQL语句输出PG数据库的数据字典】所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。