PG查询元数据,生成表数据字典:
效果:
代码:
SELECT
A .attname 字段,
concat_ws (
‘‘,
T .typname,
SUBSTRING (
format_type (A .atttypid, A .atttypmod)
FROM
‘\(.*\)‘
)
) AS 类型,
case when s.pk is not null then ‘是‘
else ‘否‘
end as 主键,
case A.attnotnull when ‘f‘ then ‘是‘
when ‘t‘ then ‘否‘
end as 空,
d.description 注释
FROM pg_attribute A
INNER JOIN pg_class C on A .attrelid = C .oid
INNER JOIN pg_type T on A .atttypid = T .oid
LEFT JOIN (SELECT conrelid, unnest(conkey) as pk
FROM pg_constraint
WHERE contype = ‘p‘) S ON S.conrelid = C .oid
AND A.attnum = S.pk
LEFT JOIN pg_description d on d.objoid = A .attrelid
AND d.objsubid = A .attnum
WHERE A .attnum > 0
AND C.relname = ‘t_gfecp_bigdata_blacklist‘
ORDER BY
C .relname,
A .attnum
使用navicat
将数据表生成数据字典
1. 场景描述
项目开发中,需要介绍数据表字段描述,使用excel
表来展示。
这时,可以使用navicat
来生成数据字典
2. postgresql
生成数据字典
- 生成数据字典的sql语句
SELECT
A.attnum AS "序号",
-- C.relname AS "表名",
-- CAST ( obj_description ( relfilenode, 'pg_class' ) AS VARCHAR ) AS "表名描述",
A.attname AS "字段名",
CASE
A.attnotnull
WHEN 't' THEN '是'
WHEN 'f' THEN '否'
END AS 是否必填,
concat_ws ( '', T.typname, SUBSTRING ( format_type ( A.atttypid, A.atttypmod ) FROM '\(.*\)' ) ) AS "数据类型",
d.description AS "注释"
FROM
pg_class C,
pg_attribute A,
pg_type T,
pg_description d
WHERE
C.relname = '需要生成数据字典的表名'
AND A.attnum > 0
AND A.attrelid = C.oid
AND A.atttypid = T.oid
AND d.objoid = A.attrelid
AND d.objsubid = A.attnum
提示:可以将其中的 C.relname = ‘需要生成数据字典的表名’
使用in
关键字,直接导出多个表
- 然后从
navicat
中将结果导出即可