说来也怪我用PostgreSQL这几年最频繁的操作反倒不是写业务SQL而是“查这个库里到底有什么”。新接手项目要梳理表结构领导突然问某个字段在哪些表出现过或者排查线上脚本连错了库这些场景全得靠一套能随时掏出来的元数据查询语句。图形化管理工具确实直观但真要面对几十个schema、几百张表还是命令行和SQL来得干净利落。这篇文章我把平时用的最多的查询方案整理出来——查表名、查表结构、查视图、判断表在哪个库、按字段名反查表全部直接跑SQL或者在psql控制台里完成。所有语句都按PostgreSQL的语法来写适合开发、运维、数据分析岗直接抄作业。后面还附了不少我在实际排查中踩过的坑建议完整看完再复制。1. 为什么查元数据这件事我坚持用SQL而不是图形工具先聊点实际的。用pgAdmin、DBeaver、Navicat这类工具鼠标点点确实能看表结构、看视图、看索引但这类操作本质上是在帮你拼SQL——工具后台还是要查information_schema和pg_catalog。问题就出在“图形化”这三个字上当你只查一两张表时图形工具很快可一旦涉及批量导出、条件过滤、跨schema对比图形界面就变得极其笨拙。举个例子前一阵我需要把某个业务库中所有包含create_time字段的表全部列出来而且还要带上每张表所在的schema、字段注释和类型。用Navicat一张一张点没有一两个小时下不来。用SQL一条语句几秒钟搞定。再比如你在服务器上排查问题往往只有一个psql终端图形工具根本连不上这时候能靠的就是一套烂熟于心的SQL。另外一个容易被忽略的点是“可复用性”。SQL写一次存成脚本文件以后换数据库环境、换项目改个连接串就能继续用。图形工具里的操作路径却很难沉淀成文档交给同事。把查询语句放在团队的知识库里谁需要谁拿去跑效率完全不一样。所以我的习惯是凡是涉及批量、过滤、自动化、脚本化获取数据库结构信息的场景一律直接写SQL。图形工具只用来做单个对象的快速浏览和手工核对。2. information_schema和pg_catalog两套“登记簿”到底看哪本PostgreSQL的元数据体系里有两套核心目录很多人用了一年可能都没分清但你看懂它们有什么区别后续所有查询都能自己推断出来。2.1 information_schema标准化但“翻译”过度的目录information_schema是SQL标准定义的视图集合PostgreSQL按标准实现了它。它的好处是字段命名友好table_name、column_name、data_type一眼就能看懂而且如果你以后切到MySQL、达梦这类数据库这套视图也能用迁移成本低。但它的缺点同样明显为了满足标准底层做了大量的类型映射和格式化处理。比如DATA_TYPE字段返回的是字符串形式的类型名CHARACTER_MAXIMUM_LENGTH这类字段在非字符类型时会填NULL。查询时一旦涉及复杂条件写出来的SQL会又长又绕。而且它只暴露了“标准”能表达的信息很多PostgreSQL特有的细节——比如物化视图、分区方式、owner、relkind——在information_schema里要么查不到要么非常费劲。2.2 pg_catalog真实的系统表和视图pg_catalog是PostgreSQL真正的系统目录它存储在一个个真实的表里比如pg_class所有表和视图、pg_attribute列信息、pg_namespaceschema信息。直接查这些表的字段名比较晦涩relname、attname、atttypid新手看了发怵但它有两个无可替代的优势第一信息全。表是不是分区表、物化视图的刷新状态、TOAST表的关联关系、统计信息全都在pg_catalog里。第二查询效率高。它是PostgreSQL内部直接使用的结构查询时通常走的是OID直接关联比information_schema那种多层视图拼接快很多。我实际使用的经验是日常要字段友好、跨库通用的信息去information_schema查要做深度分析、排查内部问题去pg_catalog查。下面所有示例里我会把两套方案的写法都给出来你根据自己的场景选。3. 查表名称和表结构最常用的四条查询语句3.1 查询当前数据库下所有表名这条基本每个PostgreSQL用户都写过。注意两个细节过滤系统schemarelkind要限定否则查出来的不止普通表还可能混进序列、索引、复合类型。-- 方式一pg_tables视图简洁版 SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY schemaname, tablename; -- 方式二pg_class pg_namespace进阶版可扩展更多字段 SELECT n.nspname AS schema_name, c.relname AS table_name, c.reltuples::bigint AS estimated_rows, -- 统计信息估算的行数 pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind r -- r表示普通表v视图m物化视图p分区表 AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname;这里解释一下为什么用pg_class能查出更多信息。pg_class里几乎所有对象都有记录——表、视图、索引、序列、分区表——区别就在relkind字段。r是普通表p是分区表v是视图m是物化视图i是索引S是序列。搞清楚这个字段后面几乎所有“查某某对象”的需求都能一招通解。3.2 查看某张表的完整结构列、类型、非空、默认值、注释图形工具看表结构很容易但你要把表结构发给同事、写进文档还是SQL更实用。我通常直接查information_schema.columnsSELECT ordinal_position AS seq, column_name, data_type, COALESCE(character_maximum_length::text, ) AS max_length, is_nullable, COALESCE(column_default, ) AS default_value, COALESCE(col_description( || table_schema || . || table_name || ::regclass, ordinal_position), ) AS column_comment FROM information_schema.columns WHERE table_schema public -- 替换成实际schema AND table_name users -- 替换成实际表名 ORDER BY ordinal_position;用系统目录函数col_description()把列注释也带出来这一手在梳理老项目时非常管用。如果只是手工快速看一眼psql里直接\d 表名更爽利\d public.users返回的信息包括列、类型、非空、默认值、索引、约束、外键一口气全给你。区别在于\d的结果是给人看的不适合程序处理。要跑脚本、做对比还是上面那套SQL。3.3 查询主键、外键、索引和约束表结构不只是列定义主键、外键、索引这些约束也是结构的一部分。这一步我强烈建议去pg_catalog查因为information_schema的约束信息分了好几个表拼接起来特别碎而pg_constraint一条语句就能看全。SELECT conname AS constraint_name, contype AS constraint_type, -- p主键, f外键, u唯一, c检查 pg_get_constraintdef(oid) AS constraint_definition FROM pg_constraint WHERE conrelid public.users::regclass ORDER BY contype, conname;索引的查询用pg_indexes视图就够了SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname public AND tablename users ORDER BY indexname;3.4 查询结果直接导出建表DDL前一阵做数据迁移客户要求按旧库结构在新库重建但要改几个字段名。我把查询语句和pg_dump --schema-only结合先生成完整的DDL再在文本编辑器里批量替换效率很高。pg_dump -h host -p 5432 -U user -d dbname \ --schema-only --tablepublic.users users_ddl.sql--schema-only只导出结构不导出数据纯文本格式方便二次编辑。4. 视图、物化视图的正确打开方式视图是PostgreSQL里日常用得最多的数据库对象之一。和查表一样查视图也有两种路径但要注意视图里有个“定义definition”字段这是它和普通表最大的区别。4.1 普通视图的查询-- 方式一pg_views标准视图 SELECT schemaname, viewname, definition FROM pg_views WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY schemaname, viewname; -- 方式二pg_class通用查询更推荐 SELECT n.nspname AS schema_name, c.relname AS view_name, pg_get_viewdef(c.oid) AS view_definition, pg_size_pretty(pg_total_relation_size(c.oid)) AS view_size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind v AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname;这里最关键的字段是pg_get_viewdef(c.oid)它返回的是视图创建语句的完整定义也就是那条SELECT语句本身。我排查视图嵌套问题时经常用这条SQL把所有视图的定义一次性导出来然后用文本搜索找某个字段在哪个视图里直接或间接被引用。4.2 物化视图的查询物化视图在PostgreSQL里是独立的relkindm它和普通视图最大的区别是“数据实际落盘”所以查询它需要看到刷新状态、是否已使用CONCURRENTLY刷新这类信息。SELECT n.nspname AS schema_name, c.relname AS matview_name, pg_get_viewdef(c.oid) AS matview_definition, c.relispopulated AS is_populated, -- true表示有数据false表示从未REFRESH过 c.reltuples::bigint AS row_count FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind m AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname;relispopulated这个字段经常被忽略。如果物化视图没有执行过REFRESH MATERIALIZED VIEW它的值是false查询时不会报错但返回的是空结果容易让人误以为是数据本身是空的。排查物化视图数据对不上的问题先看这个标志位能节省大量时间。4.3 找出“视图依赖了哪些表”查视图定义只能看到语句文本但要知道这个视图实际依赖了哪些表需要查pg_depend。这是一张依赖关系表PostgreSQL用它来管理对象之间的依赖链路。SELECT DISTINCT dependent_ns.nspname AS dependent_schema, dependent_class.relname AS dependent_view, referenced_ns.nspname AS referenced_schema, referenced_class.relname AS referenced_table FROM pg_depend JOIN pg_rewrite ON pg_depend.objid pg_rewrite.oid JOIN pg_class AS dependent_class ON pg_rewrite.ev_class dependent_class.oid JOIN pg_namespace AS dependent_ns ON dependent_class.relnamespace dependent_ns.oid JOIN pg_class AS referenced_class ON pg_depend.refobjid referenced_class.oid JOIN pg_namespace AS referenced_ns ON referenced_class.relnamespace referenced_ns.oid WHERE dependent_class.relkind v AND dependent_ns.nspname NOT IN (pg_catalog, information_schema) ORDER BY 1, 2;这条SQL我一般在重构表结构前必跑一遍能告诉你“如果删了这张表哪些视图会跟着挂掉”。比起在业务代码里一层层翻这种方式查得最彻底。5. “表属于哪个数据库”这个问题的两种真实含义“表属于哪个数据库”这个问题看起来很基础但我在工作里发现问这个问题的人往往有两种完全不同的意图。搞清楚他们真正想问什么比直接给答案更有用。5.1 含义一我连接的数据库到底叫什么名字有些同事写着写着SQL突然不确定自己连的是哪个库怕更新错数据。这时候很简单-- 当前数据库名 SELECT current_database(); -- 当前用户 SELECT current_user; -- 当前会话连接信息 SELECT inet_server_addr(), inet_server_port(), pg_backend_pid();狠一点的做法在psql里直接看一眼提示符前面显示的库名或者在SQL里加个条件错杀都不行就执行一条DELETE前先SELECT确认。有时候线上环境配了多套库连接串写错一个字母就是两个完全不同的地方这种确认永远不算多余。5.2 含义二某个数据库实例下有哪些数据库我该连哪个PostgreSQL的体系里一个实例server可以创建多个数据库表是挂在具体数据库下面的不能跨库直接访问。所以“表属于哪个数据库”在PostgreSQL体系里准确的说法应该是先确定目标表在哪个实例的哪个database下再切到那个database去查。-- 列出当前实例下所有数据库名 SELECT datname FROM pg_database ORDER BY datname; -- 查看每个数据库的大小辅助判断是不是“那个真正的库” SELECT datname, pg_size_pretty(pg_database_size(datname)) AS db_size FROM pg_database ORDER BY pg_database_size(datname) DESC;同时要理解PostgreSQL的三层命名空间实例(server) → 数据库(database) → 模式(schema) → 表。你连接一个数据库后看到的表都隶属于某个schema默认是public。所以在PostgreSQL里更常见的问题是“这张表在哪个schema下”而不是“在哪个库下”——因为当前连接的库决定了你能看到的范围而同名表可以出现在不同schema里这就引出下面这个经典坑。注意如果你真的需要跨数据库查询PostgreSQL原生是不允许dbname.schema.table这种跨库访问的。必须用 dblink 或 postgres_fdw 扩展。平时工作中我更推荐直接改连接串切库别把跨库查询这种复杂度引进来。6. 根据字段名反查所在表一条在数据治理和排查时救命的SQL这是我在实战中收到最多求助的一个需求。场景通常是这样的业务方或同事给了你一个中文描述“帮我把所有表里带‘手机号’含义的字段找出来”或者你只知道一个字段叫mobile不清楚它到底在哪些表里出现过。6.1 精确匹配和模糊匹配字段名最基础的做法是查information_schema.columns-- 精确匹配 SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE column_name user_id ORDER BY table_schema, table_name; -- 模糊匹配更实用 SELECT table_schema, table_name, column_name, data_type FROM information_schema.columns WHERE column_name ILIKE %user% ORDER BY table_schema, table_name;ILIKE是大小写不敏感的模糊匹配用%user%可以把user_id、sys_user、username、user_type这类字段一网打尽。注意column_name在系统目录里是真实存储的大小写一般字段都是小写但如果有人建表时用了双引号创建大写字段ILIKE也能匹配到正好覆盖了这个坑。6.2 从pg_attribute反查的更全面版本information_schema版本有个遗憾查不出字段注释也查不出字段在视图和物化视图里是否被引用。下面这个版本我留作了日常主力SELECT n.nspname AS schema_name, c.relname AS table_name, CASE c.relkind WHEN r THEN 普通表 WHEN p THEN 分区表 WHEN v THEN 视图 WHEN m THEN 物化视图 ELSE c.relkind::text END AS object_type, a.attname AS column_name, t.typname AS data_type, col_description(c.oid, a.attnum) AS column_comment FROM pg_attribute a JOIN pg_class c ON c.oid a.attrelid JOIN pg_namespace n ON n.oid c.relnamespace JOIN pg_type t ON t.oid a.atttypid WHERE a.attname ILIKE %user% AND a.attnum 0 -- 跳过系统隐藏列 AND NOT a.attisdropped -- 跳过已删除列 AND c.relkind IN (r, p, v, m) AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname, a.attnum;这个版本我每次给新同事讲解都会强调三个过滤条件attnum 0排除PostgreSQL内部自动生成的隐藏列比如ctid、xmin等NOT attisdropped排除曾经存在后又被删除的列relkind IN (...)只保留表和视图。这三个条件不写全结果里就会出现一堆无意义的系统残留极大干扰判断。6.3 一个实战案例通过字段反查定位问题去年排查一个数据同步问题上游推过来的JSON里有个字段叫total_amount但同步程序报了“column does not exist”错误。当时项目里有二十多个schema快照同步逻辑又走了好几层视图肉眼根本找不出到底哪里少了这个字段。就用上面那条SQL在目标schema下跑了一次ILIKE %total_amount%瞬间列出了所有带这个字段的表和视图。再逐一比对发现有一张中间表创建时字段名误写成了total_ammount多了个字母mSQL却走了另一张视图。整个过程不到十分钟定位比翻代码快多了。7. psql控制台实操绕开几个“看起来正常但结果不对”的细节命令行是PostgreSQL的免死金牌但直接上手时会遇到一些容易误判的细节。我把这些年踩过的坑集中列一下每一条都配有实际场景说明。7.1 search_path不指定schema时你以为查的表可能不是它这是PostgreSQL老生常谈的坑。同一个用户下可能用了一堆schema而你的SQL没带schema前缀这时PostgreSQL按search_path的顺序查找对象。默认路径通常是$user, public也就是说如果你当前用户是app_user它会优先在app_user这个schema里找表找不到再去public。我遇到过最惨的一次数据库里有A、B两套业务schema里面都有orders表结构却截然不同。同事用SELECT * FROM orders查了半天数据就是和报表对不上——其实他连的是A库默认走到了A的schema但他心里想的是B的那张表。安全做法-- 查看当前search_path SHOW search_path; -- 查询时始终带上schema前缀 SELECT * FROM public.orders; -- 会话内临时修改search_path SET search_path TO app_schema, public;绝大多数元数据查询场景我都建议在SQL里强制加上schema_name.前缀牺牲一点输入量换掉的是彻底的心安。7.2 表名大小写加了双引号和没加双引号是两回事PostgreSQL的标识符处理规则是未加双引号的标识符会被折叠成小写。全小写的表名直接写users没问题但如果你用CREATE TABLE Users (...)建表那这个表的真实名字是带大写字母的Users以后查询必须写成Users写users或USERS都查不到。我用SQL查元数据时也容易踩这个WHERE tablename Users匹配不到因为系统目录里真实存的是Users而字符串条件不会自动帮你折叠大小写。要稳妥地匹配表名建议统一用ILIKE而不是。7.3 控制台里表格太宽、结果截断psql查询结果如果列太宽默认会换行甚至截断看着很难受。我的习惯是遇到元数据查询、尤其是带pg_get_viewdef这种长文本字段的查询先执行\x on开启扩展显示模式每一行记录按字段列表纵向输出。这样无论字段多长都不会错位直接把结果粘贴到聊天工具、文档里也很整洁。7.4 用-E参数让psql告诉你它的内部SQLpsql 的\d、\dt、\dv这些元命令背后其实就是系统目录查询。启动psql 时加-E参数元命令执行的同时会把对应的SQL也打印出来psql -E -h host -U user -d dbname然后执行\dt你会看到psql实际是跑了哪些系统目录查询才拼接出这张表清单的。学元数据查询最快的办法就是用-E看PostgreSQL自己是怎么查的照着抄还怕学不会吗。7.5 普通用户看不到全部对象这是排查完SQL也没问题、但结果还是“缺”时第一个要怀疑的点。PostgreSQL的系统目录按权限过滤——普通用户只能看到自己有权限访问的对象pg_class、information_schema.columns都不会返回没有权限的表的记录。所以如果你用普通账号跑元数据查询发现表数量比图形工具里看到的少先别怀疑SQL写错。要么让DBA给账号授予对应权限要么用超级管理员账号验证一遍。我自己排查时通常是先用管理员身份跑一遍确认SQL没问题再切回业务账号。8. 把这套查询沉淀成团队脚本少走一半弯路元数据查询的价值在“随手能用”。单个SQL偶尔写一次不觉得什么但次数多了就会发现每一类查询都值得沉淀成固定脚本。8.1 一套最小可复用的SQL脚本模板我在每个项目里都会放一份meta_queries.sql按用途分好段落。下面是精简版结构你可以直接存下来-- -- PostgreSQL 元数据查询常用脚本持续维护 -- 用法psql -f meta_queries.sql -- -- 1) 查所有数据库 SELECT datname FROM pg_database ORDER BY datname; -- 2) 查当前schema下所有表含估算行数 SELECT c.relname, c.reltuples::bigint AS estimated_rows FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE n.nspname current_schema() AND c.relkind r ORDER BY c.relname; -- 3) 查指定表结构 SELECT a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS data_type, NOT a.attnotnull AS is_nullable, COALESCE(pg_get_expr(d.adbin, d.adrelid), ) AS default_value FROM pg_attribute a LEFT JOIN pg_attrdef d ON d.adrelid a.attrelid AND d.adnum a.attnum WHERE a.attrelid public.users::regclass AND a.attnum 0 AND NOT a.attisdropped ORDER BY a.attnum; -- 4) 查所有视图及定义 SELECT n.nspname, c.relname, pg_get_viewdef(c.oid) AS definition FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind v AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname; -- 5) 按字段名反查表 SELECT n.nspname AS schema_name, c.relname AS table_name, c.relkind AS object_type, a.attname AS column_name, t.typname AS data_type FROM pg_attribute a JOIN pg_class c ON c.oid a.attrelid JOIN pg_namespace n ON n.oid c.relnamespace JOIN pg_type t ON t.oid a.atttypid WHERE a.attname ILIKE %KEYWORD% AND a.attnum 0 AND NOT a.attisdropped AND c.relkind IN (r, p, v, m) AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname, a.attnum;脚本随项目环境变化微调但核心逻辑保持一致。放到团队wiki或者代码仓库的docs/目录下新同事接手时光这份脚本就能省掉很多熟悉环境的时间。8.2 把反查字段封装成函数如果字段反查这类操作频率特别高可以做一个简单的SQL函数把参数变成变量CREATE OR REPLACE FUNCTION public.find_columns_by_name(keyword text) RETURNS TABLE (schema_name text, table_name text, column_name text, data_type text) LANGUAGE sql AS $$ SELECT n.nspname::text, c.relname::text, a.attname::text, t.typname::text FROM pg_attribute a JOIN pg_class c ON c.oid a.attrelid JOIN pg_namespace n ON n.oid c.relnamespace JOIN pg_type t ON t.oid a.atttypid WHERE a.attname ILIKE % || keyword || % AND a.attnum 0 AND NOT a.attisdropped AND c.relkind IN (r, p, v, m) AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname, a.attnum; $$;以后直接SELECT * FROM public.find_columns_by_name(user);零成本封装比每次写一长串舒服得多。8.3 注意系统函数在不同版本的差异最后提醒一个容易忽略的版本问题pg_get_viewdef、pg_total_relation_size、pg_database_size这些函数是从很早版本就有且接口相对稳定的但pg_class.relispopulated、pg_stat_all_tables这类字段在不同大版本里可能有细微差别。如果是从PG 10、12迁移到PG 15、16的环境跑元数据脚本前先在新版本空库上验证一遍别把老脚本直接丢到生产上跑更别在老文档里查新函数。PostgreSQL的元数据查询并不难难的是形成条件反射、知道哪种场景下用哪条语句。拿着上面这些SQL跑几遍你也能做到“想要什么结构信息随手就能查出来”。后面遇到具体环境的问题欢迎在评论区带上你自己的实际报错或表结构信息来讨论。
PostgreSQL元数据查询实战：表结构、视图与字段反查SQL大全
说来也怪我用PostgreSQL这几年最频繁的操作反倒不是写业务SQL而是“查这个库里到底有什么”。新接手项目要梳理表结构领导突然问某个字段在哪些表出现过或者排查线上脚本连错了库这些场景全得靠一套能随时掏出来的元数据查询语句。图形化管理工具确实直观但真要面对几十个schema、几百张表还是命令行和SQL来得干净利落。这篇文章我把平时用的最多的查询方案整理出来——查表名、查表结构、查视图、判断表在哪个库、按字段名反查表全部直接跑SQL或者在psql控制台里完成。所有语句都按PostgreSQL的语法来写适合开发、运维、数据分析岗直接抄作业。后面还附了不少我在实际排查中踩过的坑建议完整看完再复制。1. 为什么查元数据这件事我坚持用SQL而不是图形工具先聊点实际的。用pgAdmin、DBeaver、Navicat这类工具鼠标点点确实能看表结构、看视图、看索引但这类操作本质上是在帮你拼SQL——工具后台还是要查information_schema和pg_catalog。问题就出在“图形化”这三个字上当你只查一两张表时图形工具很快可一旦涉及批量导出、条件过滤、跨schema对比图形界面就变得极其笨拙。举个例子前一阵我需要把某个业务库中所有包含create_time字段的表全部列出来而且还要带上每张表所在的schema、字段注释和类型。用Navicat一张一张点没有一两个小时下不来。用SQL一条语句几秒钟搞定。再比如你在服务器上排查问题往往只有一个psql终端图形工具根本连不上这时候能靠的就是一套烂熟于心的SQL。另外一个容易被忽略的点是“可复用性”。SQL写一次存成脚本文件以后换数据库环境、换项目改个连接串就能继续用。图形工具里的操作路径却很难沉淀成文档交给同事。把查询语句放在团队的知识库里谁需要谁拿去跑效率完全不一样。所以我的习惯是凡是涉及批量、过滤、自动化、脚本化获取数据库结构信息的场景一律直接写SQL。图形工具只用来做单个对象的快速浏览和手工核对。2. information_schema和pg_catalog两套“登记簿”到底看哪本PostgreSQL的元数据体系里有两套核心目录很多人用了一年可能都没分清但你看懂它们有什么区别后续所有查询都能自己推断出来。2.1 information_schema标准化但“翻译”过度的目录information_schema是SQL标准定义的视图集合PostgreSQL按标准实现了它。它的好处是字段命名友好table_name、column_name、data_type一眼就能看懂而且如果你以后切到MySQL、达梦这类数据库这套视图也能用迁移成本低。但它的缺点同样明显为了满足标准底层做了大量的类型映射和格式化处理。比如DATA_TYPE字段返回的是字符串形式的类型名CHARACTER_MAXIMUM_LENGTH这类字段在非字符类型时会填NULL。查询时一旦涉及复杂条件写出来的SQL会又长又绕。而且它只暴露了“标准”能表达的信息很多PostgreSQL特有的细节——比如物化视图、分区方式、owner、relkind——在information_schema里要么查不到要么非常费劲。2.2 pg_catalog真实的系统表和视图pg_catalog是PostgreSQL真正的系统目录它存储在一个个真实的表里比如pg_class所有表和视图、pg_attribute列信息、pg_namespaceschema信息。直接查这些表的字段名比较晦涩relname、attname、atttypid新手看了发怵但它有两个无可替代的优势第一信息全。表是不是分区表、物化视图的刷新状态、TOAST表的关联关系、统计信息全都在pg_catalog里。第二查询效率高。它是PostgreSQL内部直接使用的结构查询时通常走的是OID直接关联比information_schema那种多层视图拼接快很多。我实际使用的经验是日常要字段友好、跨库通用的信息去information_schema查要做深度分析、排查内部问题去pg_catalog查。下面所有示例里我会把两套方案的写法都给出来你根据自己的场景选。3. 查表名称和表结构最常用的四条查询语句3.1 查询当前数据库下所有表名这条基本每个PostgreSQL用户都写过。注意两个细节过滤系统schemarelkind要限定否则查出来的不止普通表还可能混进序列、索引、复合类型。-- 方式一pg_tables视图简洁版 SELECT schemaname, tablename FROM pg_tables WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY schemaname, tablename; -- 方式二pg_class pg_namespace进阶版可扩展更多字段 SELECT n.nspname AS schema_name, c.relname AS table_name, c.reltuples::bigint AS estimated_rows, -- 统计信息估算的行数 pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind r -- r表示普通表v视图m物化视图p分区表 AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname;这里解释一下为什么用pg_class能查出更多信息。pg_class里几乎所有对象都有记录——表、视图、索引、序列、分区表——区别就在relkind字段。r是普通表p是分区表v是视图m是物化视图i是索引S是序列。搞清楚这个字段后面几乎所有“查某某对象”的需求都能一招通解。3.2 查看某张表的完整结构列、类型、非空、默认值、注释图形工具看表结构很容易但你要把表结构发给同事、写进文档还是SQL更实用。我通常直接查information_schema.columnsSELECT ordinal_position AS seq, column_name, data_type, COALESCE(character_maximum_length::text, ) AS max_length, is_nullable, COALESCE(column_default, ) AS default_value, COALESCE(col_description( || table_schema || . || table_name || ::regclass, ordinal_position), ) AS column_comment FROM information_schema.columns WHERE table_schema public -- 替换成实际schema AND table_name users -- 替换成实际表名 ORDER BY ordinal_position;用系统目录函数col_description()把列注释也带出来这一手在梳理老项目时非常管用。如果只是手工快速看一眼psql里直接\d 表名更爽利\d public.users返回的信息包括列、类型、非空、默认值、索引、约束、外键一口气全给你。区别在于\d的结果是给人看的不适合程序处理。要跑脚本、做对比还是上面那套SQL。3.3 查询主键、外键、索引和约束表结构不只是列定义主键、外键、索引这些约束也是结构的一部分。这一步我强烈建议去pg_catalog查因为information_schema的约束信息分了好几个表拼接起来特别碎而pg_constraint一条语句就能看全。SELECT conname AS constraint_name, contype AS constraint_type, -- p主键, f外键, u唯一, c检查 pg_get_constraintdef(oid) AS constraint_definition FROM pg_constraint WHERE conrelid public.users::regclass ORDER BY contype, conname;索引的查询用pg_indexes视图就够了SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname public AND tablename users ORDER BY indexname;3.4 查询结果直接导出建表DDL前一阵做数据迁移客户要求按旧库结构在新库重建但要改几个字段名。我把查询语句和pg_dump --schema-only结合先生成完整的DDL再在文本编辑器里批量替换效率很高。pg_dump -h host -p 5432 -U user -d dbname \ --schema-only --tablepublic.users users_ddl.sql--schema-only只导出结构不导出数据纯文本格式方便二次编辑。4. 视图、物化视图的正确打开方式视图是PostgreSQL里日常用得最多的数据库对象之一。和查表一样查视图也有两种路径但要注意视图里有个“定义definition”字段这是它和普通表最大的区别。4.1 普通视图的查询-- 方式一pg_views标准视图 SELECT schemaname, viewname, definition FROM pg_views WHERE schemaname NOT IN (pg_catalog, information_schema) ORDER BY schemaname, viewname; -- 方式二pg_class通用查询更推荐 SELECT n.nspname AS schema_name, c.relname AS view_name, pg_get_viewdef(c.oid) AS view_definition, pg_size_pretty(pg_total_relation_size(c.oid)) AS view_size FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind v AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname;这里最关键的字段是pg_get_viewdef(c.oid)它返回的是视图创建语句的完整定义也就是那条SELECT语句本身。我排查视图嵌套问题时经常用这条SQL把所有视图的定义一次性导出来然后用文本搜索找某个字段在哪个视图里直接或间接被引用。4.2 物化视图的查询物化视图在PostgreSQL里是独立的relkindm它和普通视图最大的区别是“数据实际落盘”所以查询它需要看到刷新状态、是否已使用CONCURRENTLY刷新这类信息。SELECT n.nspname AS schema_name, c.relname AS matview_name, pg_get_viewdef(c.oid) AS matview_definition, c.relispopulated AS is_populated, -- true表示有数据false表示从未REFRESH过 c.reltuples::bigint AS row_count FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind m AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname;relispopulated这个字段经常被忽略。如果物化视图没有执行过REFRESH MATERIALIZED VIEW它的值是false查询时不会报错但返回的是空结果容易让人误以为是数据本身是空的。排查物化视图数据对不上的问题先看这个标志位能节省大量时间。4.3 找出“视图依赖了哪些表”查视图定义只能看到语句文本但要知道这个视图实际依赖了哪些表需要查pg_depend。这是一张依赖关系表PostgreSQL用它来管理对象之间的依赖链路。SELECT DISTINCT dependent_ns.nspname AS dependent_schema, dependent_class.relname AS dependent_view, referenced_ns.nspname AS referenced_schema, referenced_class.relname AS referenced_table FROM pg_depend JOIN pg_rewrite ON pg_depend.objid pg_rewrite.oid JOIN pg_class AS dependent_class ON pg_rewrite.ev_class dependent_class.oid JOIN pg_namespace AS dependent_ns ON dependent_class.relnamespace dependent_ns.oid JOIN pg_class AS referenced_class ON pg_depend.refobjid referenced_class.oid JOIN pg_namespace AS referenced_ns ON referenced_class.relnamespace referenced_ns.oid WHERE dependent_class.relkind v AND dependent_ns.nspname NOT IN (pg_catalog, information_schema) ORDER BY 1, 2;这条SQL我一般在重构表结构前必跑一遍能告诉你“如果删了这张表哪些视图会跟着挂掉”。比起在业务代码里一层层翻这种方式查得最彻底。5. “表属于哪个数据库”这个问题的两种真实含义“表属于哪个数据库”这个问题看起来很基础但我在工作里发现问这个问题的人往往有两种完全不同的意图。搞清楚他们真正想问什么比直接给答案更有用。5.1 含义一我连接的数据库到底叫什么名字有些同事写着写着SQL突然不确定自己连的是哪个库怕更新错数据。这时候很简单-- 当前数据库名 SELECT current_database(); -- 当前用户 SELECT current_user; -- 当前会话连接信息 SELECT inet_server_addr(), inet_server_port(), pg_backend_pid();狠一点的做法在psql里直接看一眼提示符前面显示的库名或者在SQL里加个条件错杀都不行就执行一条DELETE前先SELECT确认。有时候线上环境配了多套库连接串写错一个字母就是两个完全不同的地方这种确认永远不算多余。5.2 含义二某个数据库实例下有哪些数据库我该连哪个PostgreSQL的体系里一个实例server可以创建多个数据库表是挂在具体数据库下面的不能跨库直接访问。所以“表属于哪个数据库”在PostgreSQL体系里准确的说法应该是先确定目标表在哪个实例的哪个database下再切到那个database去查。-- 列出当前实例下所有数据库名 SELECT datname FROM pg_database ORDER BY datname; -- 查看每个数据库的大小辅助判断是不是“那个真正的库” SELECT datname, pg_size_pretty(pg_database_size(datname)) AS db_size FROM pg_database ORDER BY pg_database_size(datname) DESC;同时要理解PostgreSQL的三层命名空间实例(server) → 数据库(database) → 模式(schema) → 表。你连接一个数据库后看到的表都隶属于某个schema默认是public。所以在PostgreSQL里更常见的问题是“这张表在哪个schema下”而不是“在哪个库下”——因为当前连接的库决定了你能看到的范围而同名表可以出现在不同schema里这就引出下面这个经典坑。注意如果你真的需要跨数据库查询PostgreSQL原生是不允许dbname.schema.table这种跨库访问的。必须用 dblink 或 postgres_fdw 扩展。平时工作中我更推荐直接改连接串切库别把跨库查询这种复杂度引进来。6. 根据字段名反查所在表一条在数据治理和排查时救命的SQL这是我在实战中收到最多求助的一个需求。场景通常是这样的业务方或同事给了你一个中文描述“帮我把所有表里带‘手机号’含义的字段找出来”或者你只知道一个字段叫mobile不清楚它到底在哪些表里出现过。6.1 精确匹配和模糊匹配字段名最基础的做法是查information_schema.columns-- 精确匹配 SELECT table_schema, table_name, column_name FROM information_schema.columns WHERE column_name user_id ORDER BY table_schema, table_name; -- 模糊匹配更实用 SELECT table_schema, table_name, column_name, data_type FROM information_schema.columns WHERE column_name ILIKE %user% ORDER BY table_schema, table_name;ILIKE是大小写不敏感的模糊匹配用%user%可以把user_id、sys_user、username、user_type这类字段一网打尽。注意column_name在系统目录里是真实存储的大小写一般字段都是小写但如果有人建表时用了双引号创建大写字段ILIKE也能匹配到正好覆盖了这个坑。6.2 从pg_attribute反查的更全面版本information_schema版本有个遗憾查不出字段注释也查不出字段在视图和物化视图里是否被引用。下面这个版本我留作了日常主力SELECT n.nspname AS schema_name, c.relname AS table_name, CASE c.relkind WHEN r THEN 普通表 WHEN p THEN 分区表 WHEN v THEN 视图 WHEN m THEN 物化视图 ELSE c.relkind::text END AS object_type, a.attname AS column_name, t.typname AS data_type, col_description(c.oid, a.attnum) AS column_comment FROM pg_attribute a JOIN pg_class c ON c.oid a.attrelid JOIN pg_namespace n ON n.oid c.relnamespace JOIN pg_type t ON t.oid a.atttypid WHERE a.attname ILIKE %user% AND a.attnum 0 -- 跳过系统隐藏列 AND NOT a.attisdropped -- 跳过已删除列 AND c.relkind IN (r, p, v, m) AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname, a.attnum;这个版本我每次给新同事讲解都会强调三个过滤条件attnum 0排除PostgreSQL内部自动生成的隐藏列比如ctid、xmin等NOT attisdropped排除曾经存在后又被删除的列relkind IN (...)只保留表和视图。这三个条件不写全结果里就会出现一堆无意义的系统残留极大干扰判断。6.3 一个实战案例通过字段反查定位问题去年排查一个数据同步问题上游推过来的JSON里有个字段叫total_amount但同步程序报了“column does not exist”错误。当时项目里有二十多个schema快照同步逻辑又走了好几层视图肉眼根本找不出到底哪里少了这个字段。就用上面那条SQL在目标schema下跑了一次ILIKE %total_amount%瞬间列出了所有带这个字段的表和视图。再逐一比对发现有一张中间表创建时字段名误写成了total_ammount多了个字母mSQL却走了另一张视图。整个过程不到十分钟定位比翻代码快多了。7. psql控制台实操绕开几个“看起来正常但结果不对”的细节命令行是PostgreSQL的免死金牌但直接上手时会遇到一些容易误判的细节。我把这些年踩过的坑集中列一下每一条都配有实际场景说明。7.1 search_path不指定schema时你以为查的表可能不是它这是PostgreSQL老生常谈的坑。同一个用户下可能用了一堆schema而你的SQL没带schema前缀这时PostgreSQL按search_path的顺序查找对象。默认路径通常是$user, public也就是说如果你当前用户是app_user它会优先在app_user这个schema里找表找不到再去public。我遇到过最惨的一次数据库里有A、B两套业务schema里面都有orders表结构却截然不同。同事用SELECT * FROM orders查了半天数据就是和报表对不上——其实他连的是A库默认走到了A的schema但他心里想的是B的那张表。安全做法-- 查看当前search_path SHOW search_path; -- 查询时始终带上schema前缀 SELECT * FROM public.orders; -- 会话内临时修改search_path SET search_path TO app_schema, public;绝大多数元数据查询场景我都建议在SQL里强制加上schema_name.前缀牺牲一点输入量换掉的是彻底的心安。7.2 表名大小写加了双引号和没加双引号是两回事PostgreSQL的标识符处理规则是未加双引号的标识符会被折叠成小写。全小写的表名直接写users没问题但如果你用CREATE TABLE Users (...)建表那这个表的真实名字是带大写字母的Users以后查询必须写成Users写users或USERS都查不到。我用SQL查元数据时也容易踩这个WHERE tablename Users匹配不到因为系统目录里真实存的是Users而字符串条件不会自动帮你折叠大小写。要稳妥地匹配表名建议统一用ILIKE而不是。7.3 控制台里表格太宽、结果截断psql查询结果如果列太宽默认会换行甚至截断看着很难受。我的习惯是遇到元数据查询、尤其是带pg_get_viewdef这种长文本字段的查询先执行\x on开启扩展显示模式每一行记录按字段列表纵向输出。这样无论字段多长都不会错位直接把结果粘贴到聊天工具、文档里也很整洁。7.4 用-E参数让psql告诉你它的内部SQLpsql 的\d、\dt、\dv这些元命令背后其实就是系统目录查询。启动psql 时加-E参数元命令执行的同时会把对应的SQL也打印出来psql -E -h host -U user -d dbname然后执行\dt你会看到psql实际是跑了哪些系统目录查询才拼接出这张表清单的。学元数据查询最快的办法就是用-E看PostgreSQL自己是怎么查的照着抄还怕学不会吗。7.5 普通用户看不到全部对象这是排查完SQL也没问题、但结果还是“缺”时第一个要怀疑的点。PostgreSQL的系统目录按权限过滤——普通用户只能看到自己有权限访问的对象pg_class、information_schema.columns都不会返回没有权限的表的记录。所以如果你用普通账号跑元数据查询发现表数量比图形工具里看到的少先别怀疑SQL写错。要么让DBA给账号授予对应权限要么用超级管理员账号验证一遍。我自己排查时通常是先用管理员身份跑一遍确认SQL没问题再切回业务账号。8. 把这套查询沉淀成团队脚本少走一半弯路元数据查询的价值在“随手能用”。单个SQL偶尔写一次不觉得什么但次数多了就会发现每一类查询都值得沉淀成固定脚本。8.1 一套最小可复用的SQL脚本模板我在每个项目里都会放一份meta_queries.sql按用途分好段落。下面是精简版结构你可以直接存下来-- -- PostgreSQL 元数据查询常用脚本持续维护 -- 用法psql -f meta_queries.sql -- -- 1) 查所有数据库 SELECT datname FROM pg_database ORDER BY datname; -- 2) 查当前schema下所有表含估算行数 SELECT c.relname, c.reltuples::bigint AS estimated_rows FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE n.nspname current_schema() AND c.relkind r ORDER BY c.relname; -- 3) 查指定表结构 SELECT a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS data_type, NOT a.attnotnull AS is_nullable, COALESCE(pg_get_expr(d.adbin, d.adrelid), ) AS default_value FROM pg_attribute a LEFT JOIN pg_attrdef d ON d.adrelid a.attrelid AND d.adnum a.attnum WHERE a.attrelid public.users::regclass AND a.attnum 0 AND NOT a.attisdropped ORDER BY a.attnum; -- 4) 查所有视图及定义 SELECT n.nspname, c.relname, pg_get_viewdef(c.oid) AS definition FROM pg_class c JOIN pg_namespace n ON n.oid c.relnamespace WHERE c.relkind v AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname; -- 5) 按字段名反查表 SELECT n.nspname AS schema_name, c.relname AS table_name, c.relkind AS object_type, a.attname AS column_name, t.typname AS data_type FROM pg_attribute a JOIN pg_class c ON c.oid a.attrelid JOIN pg_namespace n ON n.oid c.relnamespace JOIN pg_type t ON t.oid a.atttypid WHERE a.attname ILIKE %KEYWORD% AND a.attnum 0 AND NOT a.attisdropped AND c.relkind IN (r, p, v, m) AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname, a.attnum;脚本随项目环境变化微调但核心逻辑保持一致。放到团队wiki或者代码仓库的docs/目录下新同事接手时光这份脚本就能省掉很多熟悉环境的时间。8.2 把反查字段封装成函数如果字段反查这类操作频率特别高可以做一个简单的SQL函数把参数变成变量CREATE OR REPLACE FUNCTION public.find_columns_by_name(keyword text) RETURNS TABLE (schema_name text, table_name text, column_name text, data_type text) LANGUAGE sql AS $$ SELECT n.nspname::text, c.relname::text, a.attname::text, t.typname::text FROM pg_attribute a JOIN pg_class c ON c.oid a.attrelid JOIN pg_namespace n ON n.oid c.relnamespace JOIN pg_type t ON t.oid a.atttypid WHERE a.attname ILIKE % || keyword || % AND a.attnum 0 AND NOT a.attisdropped AND c.relkind IN (r, p, v, m) AND n.nspname NOT IN (pg_catalog, information_schema) ORDER BY n.nspname, c.relname, a.attnum; $$;以后直接SELECT * FROM public.find_columns_by_name(user);零成本封装比每次写一长串舒服得多。8.3 注意系统函数在不同版本的差异最后提醒一个容易忽略的版本问题pg_get_viewdef、pg_total_relation_size、pg_database_size这些函数是从很早版本就有且接口相对稳定的但pg_class.relispopulated、pg_stat_all_tables这类字段在不同大版本里可能有细微差别。如果是从PG 10、12迁移到PG 15、16的环境跑元数据脚本前先在新版本空库上验证一遍别把老脚本直接丢到生产上跑更别在老文档里查新函数。PostgreSQL的元数据查询并不难难的是形成条件反射、知道哪种场景下用哪条语句。拿着上面这些SQL跑几遍你也能做到“想要什么结构信息随手就能查出来”。后面遇到具体环境的问题欢迎在评论区带上你自己的实际报错或表结构信息来讨论。
