企业官网建设流程全解析

前几天在整理一个数据平台的表结构清单，需要把整个库的表、字段、主键、分区情况全部摸一遍。这类需求在 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：信息全，但需要先理解 oid

pg_catalog 是 PostgreSQL 真正的系统目录，pg_class记录表和索引，pg_namespace记录 schema，pg_attribute记录字段，pg_constraint记录约束，pg_index记录索引和索引列。这些系统表之间大量通过oid（对象标识符）关联，而不是直接通过表名字符串关联。

刚接触的人会觉得 oid 很难受，因为平时写业务 SQL 根本不关心这个东西。但你看过几条查询就会明白，oid其实就是系统给每个对象分配的唯一编号。我给你一个最常用的表达式：'public.orders'::regclass，它能把表名直接转成 oid；反过来，oid::regclass能把 oid 转回带 schema 的表名。这个技巧贯穿所有系统表查询，务必记住。

pg_catalog 的优势在于信息完整，PostgreSQL 的很多内部细节都能从这里挖出来。比如一个字段是否为“已删除状态”（attisdropped），information_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的取值比较多，不熟悉系统表的人容易在这里踩坑。我这里列一个常用对照表：

实际业务里，我们通常只关心普通表、分区主表、外部表和视图，所以查询条件建议写成：

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、numeric；udt_name是底层内部类型名，比如varchar、int4、numeric。两者有区别，我在查 Hologres 表结构时经常发现 data_type 显示很笼统，反而udt_name更准确，所以两张字段建议一起带出来，对比着看。

character_maximum_length和numeric_precision是拿长度和精度的关键字段。比如varchar(100)会在这里显示 100，numeric(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 把表结构拼成一行 DDL：string_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_constraint：

SELECT 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_index：

SELECT 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时省略了 schema，PostgreSQL 会按 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 的元数据体系本质上同源，但细节差异确实不少。把上面这些查询脚本保存下来，接新库时先跑一遍，能帮你快速建立起对数据资产的全局认识，少走很多弯路。