【PostgreSql】生成数据字典

最新推荐文章于 2025-09-18 12:58:20 发布

转载最新推荐文章于 2025-09-18 12:58:20 发布 · 3k 阅读

6 ·

CC 4.0 BY-SA版权

原文链接：https://blog.youkuaiyun.com/WuLex

文章标签：

#postgresql #数据库 #database

Postgresql数据库专栏收录该内容

36 篇文章

订阅专栏

本文提供了一种在PostgreSQL中生成数据字典的方法，通过SQL查询表结构信息，包括字段名称、数据类型、是否必填及注释等，并介绍了如何使用Navicat工具将查询结果导出为Excel表格。

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