SQL INFORMATION_SCHEMA vs pg_catalog / mysql.innodb_* 的元数据查询规范

跨数据库迁移或写通用工具时优先用information_schema；查索引细节、函数定义、统计信息或调试性能问题时必须用pg_catalog，因其提供原生精确字段和完整类型信息。

什么时候该用 information_schema，而不是 pg_catalog

跨数据库迁移或写通用工具时，优先用 information_schema；查索引细节、函数定义、统计信息或调试性能问题时，必须切到 pg_catalog。

原因很简单：information_schema 是 sql 标准的“翻译层”，它把 postgresql 内部结构做了抽象和简化，比如所有列类型统一为 character_data、numeric 这类宽泛名称，丢失了 citext、jsonb、int4range 等真实类型信息；而 pg_catalog 返回的是原生字段（如 atttypid、attlen），能精确还原建表语句逻辑。

• information_schema.columns 不暴露列的存储方式（TOAST？压缩？）
• pg_catalog.pg_attribute 可以配合 pg_type 查出完整类型 OID 和变长/定长标识
• 想查某索引是否是唯一、部分、表达式索引？pg_indexes（属于 pg_catalog）才带 indexdef 字段，information_schema 里压根没有这个维度

pg_catalog 查询必须绕开的三个权限/可见性陷阱

直接查 pg_catalog 表，常遇到“查不到”或“结果为空”，不是语法错，而是权限或搜索路径限制。

• 默认不显示其他 schema 的对象：即使你有读权限，pg_tables 默认只返回当前 search_path 中的非系统 schema 表；要查全量，得加 WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
• pg_stat_* 类视图（如 pg_stat_user_tables）只返回当前用户拥有的或已授 select 权限的表——没授权就看不到行数、seq_scan 次数等关键指标
• 系统表字段可能被权限遮蔽：例如 pg_proc.prosrc（函数源码）对非超级用户返回 <insufficient privilege></insufficient>，而非空值或 NULL

mysql 的 INFORMATION_SCHEMA 和 INNODB_* 表根本不是一回事

MySQL 的 INFORMATION_SCHEMA 是只读视图集合，行为接近 PostgreSQL 的同名 schema；但 INNODB_*（如 INNODB_TRX、INNODB_LOCK_WAITS）是 InnoDB 存储引擎暴露的实时状态快照，不属于标准元数据范畴。

• INFORMATION_SCHEMA.TABLES 返回的是表定义层面信息（行数是估算值），而 INNODB_SYS_TABLES 返回的是引擎内部的 table_id、space_id 等物理标识
• INNODB_TRX 里的 trx_state、trx_started 是秒级实时的事务状态，PostgreSQL 要查类似信息得组合 pg_stat_activity + pg_locks + pg_blocking_pids()
• 别指望用 MySQL 的 INNODB_* 去类比 PostgreSQL 的 pg_stat_database 或 pg_statio_user_tables：前者是引擎层诊断接口，后者是服务层统计聚合，设计目标完全不同

一个安全又精准的元数据查询习惯

不要在生产环境裸写 SELECT * FROM pg_catalog.pg_class ——字段太多、含义太隐晦，容易误读。推荐组合使用：

• 先用 dt+（psql 元命令）快速看表大小、行数、索引数，这是最贴近 dba 直觉的方式
• 需要写脚本批量分析时，固定用 pg_catalog 的三张核心表：pg_class（对象基本信息）、pg_namespace（schema 映射）、pg_attribute（列定义），再 LEFT JOIN pg_type 补类型名
• 如果必须兼容 MySQL，只用 INFORMATION_SCHEMA 的最小交集字段：比如 table_name、column_name、data_type、is_nullable；其余一律视为不可靠，尤其避免依赖 character_maximum_length 或 numeric_precision 的值

真正难的不是查出来，而是判断哪一行代表哪个逻辑对象——比如 pg_class.relkind = 'i' 是索引，但 'I' 是分区索引，'c' 是复合类型，这些字符编码没人会背，查的时候顺手 SELECT * FROM pg_class WHERE oid = xxx 看一眼 relkind 注释更省事。