前几天在整理一个数据平台的表结构清单需要把整个库的表、字段、主键、分区情况全部摸一遍。这类需求在 PostgreSQL 里不算难但一旦放进 Hologres 这类兼容 PostgreSQL 协议的仓库型数据库里问题就来了同样是查表结构两边能用的查询语句不完全一样甚至同一条 SQL在一端能跑在另一端返回空。我把这段时间用过的 PostgreSQL、Hologres 表结构查询语句整理成了一份实践笔记。这篇文章适合两类人看一类是刚接手别人维护的 PostgreSQL 库需要快速盘点库里有哪些表、表里有哪些字段、主键和索引是怎么建的另一类是在 Hologres 环境里做数据开发发现网上抄来的元数据查询语句经常“失灵”想搞清楚到底哪些查询能用、哪些该绕过。读完你至少能拿到一整套直接可跑的查询语句也明白为什么有些写法在 Hologres 里会翻车。1. 元数据在哪里information_schema 与 pg_catalog 的取舍1.1 information_schema符合标准但经常被“阉割”information_schema 是 SQL 标准里定义的元数据视图集合像information_schema.tables、information_schema.columns、information_schema.table_constraints这些都是我们最熟悉的查询入口。它的好处是语义清晰字段名一看就懂比如is_nullable、column_default、table_schema不需要理解底层存储结构。但是用过一段时间你就会发现information_schema 也有明显的局限。首先是慢当库里表特别多的时候它底层要做大量的视图嵌套和类型转换查询效率明显不如直接读系统目录其次是信息不够全像列的压缩方式、存储类型、字符集排序规则、表的数据分布方式这些在 PostgreSQL 或 Hologres 里有价值的元数据information_schema 里根本没有对应字段。更让人头疼的是某些数据库对 information_schema 的表做了“阉割兼容”——视图存在但返回的行是空的。我在某些兼容 PostgreSQL 协议的数据库上就遇到过information_schema.columns能查出字段但information_schema.table_constraints永远返回空的情况。所以我的习惯是字段基本信息用 information_schema省事、可读性好约束、索引、表物理属性这类信息必须直接用 pg_catalog 系统表。两条路各有分工别在一个方案上死磕。1.2 pg_catalog信息全但需要先理解 oidpg_catalog 是 PostgreSQL 真正的系统目录pg_class记录表和索引pg_namespace记录 schemapg_attribute记录字段pg_constraint记录约束pg_index记录索引和索引列。这些系统表之间大量通过oid对象标识符关联而不是直接通过表名字符串关联。刚接触的人会觉得 oid 很难受因为平时写业务 SQL 根本不关心这个东西。但你看过几条查询就会明白oid其实就是系统给每个对象分配的唯一编号。我给你一个最常用的表达式public.orders::regclass它能把表名直接转成 oid反过来oid::regclass能把 oid 转回带 schema 的表名。这个技巧贯穿所有系统表查询务必记住。pg_catalog 的优势在于信息完整PostgreSQL 的很多内部细节都能从这里挖出来。比如一个字段是否为“已删除状态”attisdroppedinformation_schema 里根本没有这个概念但实际迁移库、对比表结构时这种细节恰恰能救命。缺点是裸系统表查询需要手动 join而且不同版本之间字段有细微差异但这并不影响它成为查表结构的首选数据源。1.3 Hologres 的兼容层边界Hologres 对外宣称兼容 PostgreSQL 协议实际使用下来DML 和普通查询的兼容度很高元数据查询则是“大部分兼容小部分要绕路”。具体来说information_schema.tables、information_schema.columns在 Hologres 里基本可用pg_class、pg_namespace、pg_attribute、pg_constraint、pg_index这些系统表也存在字段含义大体一致。但 Hologres 毕竟是仓储型数据库它有一套自己的表模型比如行存/列存、分布式键、聚类键、生命周期、表组这些概念在 PostgreSQL 里根本没有所以你在 pg_catalog 里查不到得去找 Hologres 自己的系统视图。我在下面的章节里会把 PostgreSQL 的查询语句和 Hologres 的差异点分开写。凡是两边通用或基本通用的我直接给标准 SQL凡是 Hologres 需要特殊处理的我会明确指出替代方案并且说明“为什么这条语句在 Hologres 里不适用”。这样你在切换环境时不至于抓瞎。2. 表清单查询从 schema 到表类型、所有者、估算行数2.1 最小可用的表清单pg_class pg_namespace先来解决第一个问题这个库到底有哪些表最简单靠谱的写法是直接查 pg_class 和 pg_namespace。SELECT n.nspname AS schema_name, c.relname AS table_name, c.relkind AS table_kind, pg_get_userbyid(c.relowner) AS owner, c.reltuples::bigint AS estimated_rows, obj_description(c.oid, pg_class) AS table_comment FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE n.nspname NOT LIKE pg_% AND n.nspname information_schema ORDER BY n.nspname, c.relname;这段查询有几个地方值得解释。relkind是表类型最关键reltuples是优化器估算的行数不是精确值但拿来判断表的大小规模非常方便obj_description(c.oid, pg_class)能取出通过COMMENT ON TABLE写入的中文注释。很多公司的表注释就是业务口径比字段名还有价值查表清单时一定要带出来。2.2 按类型收窄relkind 的正确打开方式relkind的取值比较多不熟悉系统表的人容易在这里踩坑。我这里列一个常用对照表relkind含义适用场景r普通表最常见的业务表p分区主表PostgreSQL 原生分区表i索引通常需要过滤掉S序列通常需要过滤掉v视图需要和表区分开m物化视图需要和表区分开f外部表FDW 映射的表c组合类型通常需要过滤掉tTOAST 表系统内部使用实际业务里我们通常只关心普通表、分区主表、外部表和视图所以查询条件建议写成WHERE n.nspname NOT LIKE pg_% AND n.nspname information_schema AND c.relkind IN (r, p, f, v, m)这里特别说明一下如果你用的是 PostgreSQL 11 以上版本分区主表的 relkind 是p分区子表的 relkind 是r。如果只过滤r你会发现分区表的子表全都混进来主表反而看不到。我在一个项目里就吃过这个亏当时统计表数量把几百个分片子表全算进去了最后交出去的盘点表惨不忍睹。所以查表清单时先想想库里到底有没有分区表。2.3 Hologres 表清单pg_catalog 不够得上 hg_relation_info在 Hologres 里上面的 pg_class 查询也能跑但你会发现它只能返回比较基础的 schema、表名、owner 信息。真正的 Hologres 表属性比如表存储模型行存还是列存、分布式键是什么、聚类键是什么、生命周期是多少天、是否在默认表组这些全部不在 pg_class 里。Hologres 提供了一套自己的元数据视图/函数基本都在hologres这个 schema 下。我记得比较实用的有hologres.hg_relation_info它可以把单表的存储模型、分布键、聚类键、生命周期等信息一并返回。你可以这样用SELECT * FROM hologres.hg_relation_info WHERE schema_name public AND table_name orders;不同的 Hologres 版本这类视图的字段名可能略有差异但大方向一致PostgreSQL 通用元数据负责“表存在不存在”Hologres 自己的系统视图负责“表怎么存储、怎么分布”。如果你在某个 Hologres 版本里发现查询报错或字段名对不上优先去查对应版本的系统表文档而不是怀疑自己的 SQL 写错了。我在升级 Hologres 版本后遇到过视图字段名字段变长的情况旧脚本直接失效这点要有心理准备。2.4 按名字快速搜表适合表多到记不清的场景表多的时候你通常会连表名都记不全只记得大概叫ods_user或者dwd_order。这时候用正则表达式去过滤是最快的SELECT n.nspname AS schema_name, c.relname AS table_name, c.relkind FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relname ~* user|order|log AND c.relkind IN (r, p, f) ORDER BY c.relname;~*是 PostgreSQL 的正则匹配大小写不敏感。这个写法在 Hologres 里同样适用因为内核兼容了正则操作符。注意一点表特别多时全表扫描 pg_class 本身很快不用担心性能通常都是毫秒级返回。3. 字段列表查询拼出像 \d 一样完整的列信息3.1 通用做法information_schema.columns 直接抄作业如果要快速看某张表的字段清单我建议优先用 information_schema.columns它最接近人的直觉SELECT ordinal_position, column_name, data_type, udt_name, character_maximum_length, numeric_precision, numeric_scale, is_nullable, column_default, is_identity FROM information_schema.columns WHERE table_schema public AND table_name orders ORDER BY ordinal_position;这段查询里data_type是面向业务的类型名比如character varying、integer、numericudt_name是底层内部类型名比如varchar、int4、numeric。两者有区别我在查 Hologres 表结构时经常发现 data_type 显示很笼统反而udt_name更准确所以两张字段建议一起带出来对比着看。character_maximum_length和numeric_precision是拿长度和精度的关键字段。比如varchar(100)会在这里显示 100numeric(10,2)会在 precision 和 scale 两个字段里分别显示 10 和 2。这些在写下游数据同步脚本时经常要用到比如生成建表语句、对比目标表字段长度。3.2 进阶做法pg_attribute pg_attrdef col_description如果你觉得 information_schema 不够用比如想同时拿到字段注释、字段是否被删除、字段存储参数那就要用系统表组合查询。下面的写法比较长但信息密度最高SELECT a.attnum AS ordinal_position, a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull AS not_null, COALESCE(pg_get_expr(d.adbin, d.adrelid), ) AS default_value, COALESCE(col_description(a.attrelid, a.attnum), ) AS column_comment, a.attstorage AS storage_type, a.attidentity AS identity_type FROM pg_attribute a LEFT JOIN pg_attrdef d ON d.adrelid a.attrelid AND d.adnum a.attnum WHERE a.attrelid public.orders::regclass AND a.attnum 0 AND NOT a.attisdropped ORDER BY a.attnum;这段查询里有几个容易忽略的点。attnum 0是为了排除 PostgreSQL 内部加的隐藏列比如ctid、xmin、xmax等NOT a.attisdropped是为了排除被ALTER TABLE DROP COLUMN删除后残留的字段。如果没有这两个条件你查出来的字段列表会和实际对不上。format_type(a.atttypid, a.atttypmod)能把类型转成带长度的可读形式比如character varying(32)、numeric(10,2)这是我最常用的函数。col_description则是取字段注释的唯一入口注意它和取表注释的obj_description参数不同别搞混。3.3 把表结构拼成一行 DDLstring_agg 的加分用法有时候我们需要快速生成一张表的建表语句片段或者把字段列表拖出来给其他工具用。这时候可以用 string_agg 把字段拼接成一行SELECT string_agg( column_name || || data_type || CASE WHEN is_nullable NO THEN NOT NULL ELSE END || CASE WHEN column_default IS NOT NULL THEN DEFAULT || column_default ELSE END, , ORDER BY ordinal_position ) AS ddl_columns FROM information_schema.columns WHERE table_schema public AND table_name orders;这个写法在 PostgreSQL 和 Hologres 里都能跑。我一般用它在两张表之间做结构对比把两个库的字段串分别拼出来再去 diff 一致性。注意 data_type 在 Hologres 里可能返回的是text这类通用类型如果你需要精确的原始类型建议改用 udt_name。4. 主键、默认值、索引约束类信息三条路4.1 主键和唯一约束不要在 information_schema 里绕圈子主键查询是元数据查询里最常被问到的需求。information_schema 理论上有table_constraints和key_column_usage两张视图可以做但我在 Hologres 里实测经常会返回空结果所以后来直接改用 pg_constraintSELECT c.conname AS constraint_name, c.contype AS constraint_type, pg_get_constraintdef(c.oid) AS constraint_def, array_agg(a.attname ORDER BY u.ord) AS constraint_columns FROM pg_constraint c CROSS JOIN LATERAL unnest(c.conkey) WITH ORDINALITY AS u(attnum, ord) JOIN pg_attribute a ON a.attrelid c.conrelid AND a.attnum u.attnum WHERE c.conrelid public.orders::regclass AND c.contype IN (p, u) GROUP BY c.conname, c.contype, pg_get_constraintdef(c.oid);这段里的contype含义很直观p是主键u是唯一约束f是外键c是 check 约束。conkey是一个数组存储的是字段序号需要把它展开成字段名所以用了unnest配合WITH ORDINALITY。pg_get_constraintdef会直接生成完整的约束定义字符串比如PRIMARY KEY (id, order_date)这在做结构对比时特别省事。要注意的是PostgreSQL 支持复合主键array_agg收集出来的是多个字段名不要只看第一个字段就下结论。4.2 默认值与生成列识别自增和 identity 字段建表时我们会给字段设置默认值常见的有固定值、函数调用、序列调用三种。区分它们很重要因为自增主键的默认值本质上是nextval(xxx_seq::regclass)如果你把默认值原样搬到另一个库可能因为序列不存在而直接报错。SELECT a.attname, pg_get_expr(d.adbin, d.adrelid) AS default_expr, a.attidentity, a.attgenerated FROM pg_attribute a LEFT JOIN pg_attrdef d ON d.adrelid a.attrelid AND d.adnum a.attnum WHERE a.attrelid public.orders::regclass AND a.attnum 0 AND NOT a.attisdropped AND (d.adbin IS NOT NULL OR a.attidentity OR a.attgenerated ) ORDER BY a.attnum;attidentity如果是a或d说明字段是用GENERATED ALWAYS AS IDENTITY或GENERATED BY DEFAULT AS IDENTITY定义的attgenerated如果是s说明是生成列generated column。这两类字段的默认值语义和普通默认值完全不同做数据迁移时要特别小心。Hologres 里这类写法原理相同但如果你发现 pg_attrdef 返回空大概率是因为表是在 Hologres 控制台或外部工具里创建的内部存储方式和原生 PostgreSQL 有差异。4.3 索引清单pg_indexes 与 pg_get_indexdef索引信息最省事的查询是直接用 pg_indexes 视图SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname public AND tablename orders ORDER BY indexname;indexdef会直接生成完整的CREATE INDEX语句包含索引类型、索引列、排序方向、条件部分索引等所有信息。比如部分索引会显示WHERE deleted_at IS NULL表达式索引会显示USING btree (lower(email))一眼就能看明白索引到底是建在什么基础上。如果你需要更底层的信息比如索引列的顺序和排序方向可以查 pg_indexSELECT ic.relname AS index_name, a.attname AS column_name, x.indoption AS index_option FROM pg_index x JOIN pg_class c ON c.oid x.indrelid JOIN pg_class ic ON ic.oid x.indexrelid CROSS JOIN LATERAL unnest(x.indkey) WITH ORDINALITY AS k(attnum, ord) JOIN pg_attribute a ON a.attrelid c.oid AND a.attnum k.attnum WHERE c.oid public.orders::regclass ORDER BY ic.relname, k.ord;这个查询会有一个值得注意的地方如果某个索引列是函数表达式而不是普通字段indkey里的 attnum 会是 0此时 join pg_attribute 会失败该列会消失。所以遇到表达式索引第一段基于 pg_indexes 的查询更靠谱系统表查询要谨慎。5. 分区表、外表、Hologres 专有属性信息盲区清扫5.1 PostgreSQL 分区表主表、子表与分区边界有分区表的库表结构比普通表复杂得多。查表清单时分区主表的 relkind 是p每个分片子表是r。主表能告诉我们分区键是什么、分区策略是什么子表能告诉我们每个分区的边界。查主表分区键和策略SELECT pt.partrelid::regclass AS table_name, pt.partstrat AS partitioning_strategy, array_agg(pa.attname ORDER BY ord) AS partition_keys, pg_get_expr(pt.partexprs, pt.partrelid) AS partition_expr FROM pg_partitioned_table pt CROSS JOIN LATERAL unnest(pt.partattrs) WITH ORDINALITY AS u(attnum, ord) JOIN pg_attribute pa ON pa.attrelid pt.partrelid AND pa.attnum u.attnum WHERE pt.partrelid public.orders::regclass GROUP BY pt.partrelid, pt.partstrat, pg_get_expr(pt.partexprs, pt.partrelid);partstrat的取值含义h是哈希分区l是列表分区r是范围分区。partattrs是分区键字段序号数组和前面查主键类似需要 unnest 展开。查所有分片子表及其边界SELECT child_ns.nspname AS child_schema, child.relname AS child_name, pg_get_expr(child.relpartbound, child.oid) AS partition_bound FROM pg_inherits i JOIN pg_class parent ON parent.oid i.inhparent JOIN pg_class child ON child.oid i.inhrelid JOIN pg_namespace child_ns ON child_ns.oid child.relnamespace WHERE parent.oid public.orders::regclass ORDER BY child.relname;relpartbound存储的就是分区边界表达式比如FOR VALUES FROM (2025-01-01) TO (2025-02-01)用pg_get_expr转出来之后直接就能读懂每个子表覆盖的数据范围。用这个查询你可以很快找出漏建的分区或核对新分区边界是否正确。5.2 Hologres 分区从裁剪结果反推分区信息Hologres 的分区表和 PostgreSQL 原生分区表不一样。你通过 information_schema 或 pg_class 查询时看到的通常只有主表真正承载数据的分区子表在内部分区存储里不会暴露成普通表。因此前面那套基于 pg_inherits 的查询在 Hologres 里基本查不到东西。我的习惯是两条路配合第一步先看有没有按时间分区的字段这能通过 information_schema.columns 里找到分区字段第二步直接跑一个EXPLAIN看查询计划里实际扫描了哪些分区这是最直观的验证方式。EXPLAIN SELECT * FROM public.orders WHERE order_date 2025-01-15;如果分区裁剪成功执行计划里只会出现对应分区的名字如果裁剪没生效你会看到扫描了全部分区这通常意味着分区键没传或者查询写法导致无法裁剪。这条经验在任何分析型数据库里都通用Hologres 也一样。通过 EXPLAIN 反推分区信息有时候比查系统表更可靠。5.3 外部表结构查询与 FDW 的选项查看如果你在 PostgreSQL 里使用 foreign data wrapper 映射了外部数据源表在 pg_class 里的 relkind 是f。查外部表的信息光看普通字段列表不够更重要的是看它是通过哪个 FDW 建立的、远端表名是什么。SELECT c.relname AS foreign_table_name, f.foreignserverid::regclass AS server_name, pg_get_userbyid(f.owner) AS owner, f.ftoptions AS options FROM pg_foreign_table f JOIN pg_class c ON c.oid f.ftrelid WHERE c.relname remote_table AND c.relkind f;ftoptions里通常保存了远端 schema 名、远端表名这些映射信息比如schema_name和table_name。如果你发现外部表查询报“relation does not exist”多半是这两个配置和远端实际情况对不上。Hologres 同样支持外部表查询逻辑类似但选项字段名可能随数据源不同而变化。6. 固化成库表字典工具几个值得长期保存的脚本片段6.1 用视图固化常用元数据查询表结构查询写一次容易但每次都要完整复制可能会很麻烦。我的做法是把高频查询固化成视图比如建一个 v_table_dict把前面所有字段信息拼接成一张宽表CREATE OR REPLACE VIEW public.v_table_dict AS SELECT n.nspname AS table_schema, c.relname AS table_name, obj_description(c.oid, pg_class) AS table_comment, a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull AS not_null, COALESCE(pg_get_expr(d.adbin, d.adrelid), ) AS default_value, COALESCE(col_description(a.attrelid, a.attnum), ) AS column_comment FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace JOIN pg_attribute a ON a.attrelid c.oid LEFT JOIN pg_attrdef d ON d.adrelid c.oid AND d.adnum a.attnum WHERE c.relkind r AND n.nspname NOT LIKE pg_% AND n.nspname information_schema AND a.attnum 0 AND NOT a.attisdropped;之后只需要一条 SQL 就能拿到整个库的表结构字典还能直接导入 Excel 给业务核对字段含义。Hologres 里类似的视图也能建但如果使用了 hg_relation_info 的专有字段建议单独建视图避免每次查询都执行代价较高的系统函数。6.2 权限、大小写、搜索路径三个最隐蔽的坑我在使用这套查询语句时踩过几个坑逐个说一下希望对你有帮助。第一个是权限问题。如果你用一个低权限账号连接数据库查询 pg_class 或 information_schema 时通常不会报错而是看不到完整数据。比如同一个查询超级用户能看到 2000 张表应用账号只能看到其中的 100 张这种静默差异非常容易让你误判库的真实规模。排查方法很简单先对比当前账号的角色和权限再决定是否信任查询结果。第二个是大小写问题。PostgreSQL 对未加引号的标识符会自动转成小写如果你用大写字母建了表名直接写WHERE table_name ORDERS查不到表。规范的做法是统一用小写表名搜索时优先用~*正则或lower()函数兜底。第三个是 search_path。如果你执行public.orders::regclass时省略了 schemaPostgreSQL 会按 search_path 找表。同一个表名在多个 schema 下都存在时结果可能不是你想要的。建议所有表结构查询都带上 schema 前缀别偷懒。6.3 我保存的查表结构脚本片段最后分享几个我长期保存的脚本片段都是高频使用的。在 psql 环境里其实\d table_name是最好用的工具能看到字段、类型、非空、默认值、索引、外键、触发器等所有信息。但在 Hologres 里\d的完整度会差一些所以我更多依赖 SQL。下面这个脚本是我接新库时必跑的第一条SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_schema NOT IN (pg_catalog, information_schema, hologres) ORDER BY table_schema, table_name;跑完它再跑 v_table_dict 生成字段明细最后查一遍 pg_constraint 确认主外键关系一整套表结构盘点就算完成了。如果你每天都和元数据打交道建议把这几条 SQL 存成 shell 函数用表名作为参数动态拼 SQL能省下一大堆复制粘贴的时间。我在实际使用中发现表结构查询语句最值钱的地方不在于“写得有多炫”而在于你清楚每条查询背后的数据来源以及它在不同数据库下的适用边界。PostgreSQL 和 Hologres 的元数据体系本质上同源但细节差异确实不少。把上面这些查询脚本保存下来接新库时先跑一遍能帮你快速建立起对数据资产的全局认识少走很多弯路。