PostgreSQL查看表结构全攻略：从实例到列，系统表与psql元命令详解

1. 拿到一个PG实例，先看清它装了什么

事情是这样的，前几天同事丢给我一个 PostgreSQL 连接串，说是"帮我看下这个库里都有什么表"。我习惯性敲了\dt，结果屏幕上干干净净，什么都没有。第一反应是权限不够，查了一下才发现，表根本不在 public 模式下。这个插曲很典型——PostgreSQL 的对象层级是"实例 → 数据库 → 模式（schema） → 表 → 列"，比 MySQL 的"实例 → 数据库 → 表"多了一层。很多从 MySQL 转过来的人，第一步就在这儿迷路了。

所以这篇东西我不打算只罗列命令，而是按照"从实例一路看到列"的顺序，把查看数据库和表结构的常用手段系统过一遍。无论你是做课程设计、日常运维，还是刚从 MySQL 切到 PG，照着走一遍就能对实例里的东西门儿清。

1.1 版本和实例信息：一切查询的前提

连上 PG 的第一件事，先搞清楚你连的是什么版本、什么角色。版本不同，有些系统视图的字段会有差异。比如pg_stat_user_tables里的n_live_tup在 PG 12 之后表现更准确，而pg_index的indisvalid从很早就有了，但 PG 14 之后对CONCURRENTLY建索引的校验更严格。所以查看版本是第一步：

SELECT version();

这个命令会返回完整的版本字符串，包括 PostgreSQL 版本号和编译信息。配合当前连接信息一起看：

SELECT current_database(), current_user, session_user;

current_database()返回当前会话所在的数据库名，current_user和session_user的区别在于，current_user可能是通过SET ROLE切换后的角色，而session_user是登录时的原始角色。检查权限问题的时候，这两个值经常能帮上大忙。

1.2 数据库列表：psql 元命令与系统表对照

查看当前实例下有哪些数据库，最直接的方式是 psql 的\l或\l+：

\l

\l+还会额外显示每个库的磁盘占用大小、表空间和描述信息。对应的 SQL 查询是：

SELECT datname, datdba, encoding, datcollate, datctype FROM pg_database;

datdba是数据库属主的 OID，想显示具体用户名可以关联pg_roles表。encoding是库的编码格式，常见的有 UTF8，datcollate和datctype是排序规则和字符分类规则，这两个参数在建库之后就不能轻易修改，直接决定了字符串比较和排序的行为。

这里要特别提醒：pg_database里能看到所有数据库的列表，但你的角色并不一定有权限访问其中的数据。看到列表不等于能进去，真正操作时还是需要库级别的 CONNECT 权限。

1.3 和 MySQL 的直观差异

从 MySQL 过来的人对SHOW DATABASES很熟悉。在 PG 里没有直接等价的关键字，\l是最接近的体验。而USE database_name这个切换命令在 PG 里也不存在，PG 只能在连接时指定数据库，或者断开重连。连接时指定库的方式是：

psql -h host -p port -U username -d database_name

连接后想换库，只能退出再重连，或者用\c database_name（psql 的元命令，本质上是重新建立了一次连接，其实也会校验连接权限）。这个设计差异不算坑，但新人经常会在这里疑惑"为什么我 use 不了"。

2. schema是表结构的"命名空间"：看不到表多半是它的问题

之前我\dt查不到表，就是因为表的归属模式不是默认的 public。PostgreSQL 的 schema 概念，本质上相当于操作系统里面的目录：数据库是"整个磁盘"，schema 就是"文件夹"，表是"文件"。同一张表在数据库里是否同名，取决于它归属于哪个 schema。 查看命令是\d，不带参数时显示当前模式下的所有可见对象（表、视图、索引、序列等）。\dt显示表，\dt+显示表及其 OID、大小和描述信息。这和\d的粒度不一样，\d是"所有对象混合在一起"，\dt专门查表。对应 SQL：

SELECT schemaname, tablename, tableowner, tablespace FROM pg_tables WHERE schemaname = 'public';

这里pg_tables视图已经帮我们过滤好了类型，只看表不看索引和序列。schemaname是模式名，tablename是表名，tableowner是属主，tablespace是表空间（NULL 表示使用默认表空间）。

\dt还支持通配符模式过滤。比如只查users开头的表：

\dt users*

这个通配符和 SQL 里的 LIKE 模式不完全一样，需要匹配的是模式名.表名，例如所有模式下的 users 表：\dt *.*users*。psql 元命令的匹配规则默认是接在.后面那一段才算表名，需要跨模式搜索的时候最好加上*.*。

3.2 information_schema 与 pg_catalog：两个查询入口的差别

除了pg_tables，还有一套 SQL 标准视图叫information_schema，它最初设计出来是为了兼容不同数据库的查询习惯。

SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name;

information_schema.tables里除了表，还有视图（table_type='VIEW'）和外部表（FOREIGN）。

两套体系怎么选？我的经验是：日常 psql 里干活用\dt最省事；写自动化脚本、检测表是否存在这类场景用pg_tables更可靠，因为information_schema某些字段在不同版本之间存在细微差异；而需要严格遵循 SQL 标准、跨数据库移植时，information_schema是唯一选择。注意一点：information_schema默认只显示当前用户有权限访问的对象，而pg_catalog在大多数情况下需要配合权限过滤条件才能看到全部对象，否则受行级安全策略影响，列表并不完整。

3.3 行数与活性：哪些表真正被用过

光看到"有哪些表"还不够，很多时候你还想知道这些表到底有多少数据、什么时候被更新过。这时就要查统计信息：

SELECT schemaname, relname, n_live_tup, n_dead_tup, last_vacuum, last_autovacuum FROM pg_stat_user_tables ORDER BY n_live_tup DESC;

n_live_tup是表里的活跃行数估计值，n_dead_tup是死行数（更新或删除后残留的旧版本）。死行过多说明该表需要 VACUUM。last_autovacuum显示自动清理的时间，如果一张表常年没有 autovacuum，且死行持续增长，通常意味着自动清理在这个库上被关闭了，或者表过大导致清理频率不足。

需要注意的是，n_live_tup是估算值，不是精确的 COUNT(*)。对于精确行数，可以直接SELECT count(*) FROM 表名，但在大表上这个操作会全表扫描，代价比较高。日常监控和容量规划场景，统计信息完全够用。

4. 深入到列：类型、默认值、约束一次看全

表找到了，接下来就是翻转出每个字段的细节。这是 PostgreSQL 和 MySQL 差异比较明显的地方之一：MySQL 的DESCRIBE table返回的结果比较粗，PG 的\d table则是把列、索引、外键、触发器混合在一起展示，信息密度更高，但一开始会让人有点摸不着头脑。

4.1 \d 与 \d+ 的信息拼图

假设表名是public.users，在 psql 里执行：

\d public.users

会看到类似这样的输出：

Table "public.users" Column | Type | Collation | Nullable | Default ------------+------------------------+-----------+----------+--------- id | integer | | not null | nextval('users_id_seq'::regclass) username | character varying(64) | | not null | email | character varying(255) | | | created_at | timestamp with time zone | | | now() Indexes: "users_pkey" PRIMARY KEY, btree (id) "users_username_key" UNIQUE CONSTRAINT, btree (username)

这个输出把列和索引都列出来了。Nullable字段显示not null表示非空。Default里如果有nextval('序列名'::regclass)，说明列是序列自增的。timestamp with time zone是带时区的时间戳，缩写是timestamptz。

\d+还会显示列的注释（如果存在的话）以及表的 OID。日常快速浏览结构时，\d够了；要确认某个字段是否有注释、或者奇怪的字符类型长度，\d+更全面。

4.2 用系统表查列：format_type 与 pg_attribute

psql 的\d背后，实际上是查询了系统表。当你需要把列信息嵌入到脚本或 SQL 报表中时，就要自己写查询了。最常用的查询是：

SELECT a.attname AS column_name, format_type(a.atttypid, a.atttypmod) AS data_type, a.attnotnull AS not_null, a.attdefault AS default_value, d.description AS column_comment FROM pg_attribute a LEFT JOIN pg_description d ON d.objoid = a.attrelid AND d.objsubid = a.attnum WHERE a.attrelid = 'public.users'::regclass AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum;

解释一下几个关键点：

• a.attrelid = 'public.users'::regclass：这里把字符串直接转成 regclass 类型，PostgreSQL 会自动解析出表的 OID。如果你不加 schema 前缀，它会依赖当前的search_path。所以脚本里最好写明 schema。
• format_type(a.atttypid, a.atttypmod)：这个函数把类型 OID 和修饰符组合成人类可读的完整类型名，比如character varying(64)。直接查a.atttypid只能拿到 OID 数字，没法看。
• attnum > 0：系统列的attnum是负数（比如ctid、xmin这些），我们要过滤掉。
• attisdropped：被 DROP COLUMN 后，列不会立刻物理删除，而是标记为 dropped。过滤条件是NOT a.attisdropped。
• attdefault：默认值表达式，如果没有默认值则为 NULL。注意它显示的是表达式本身，比如nextval(...)或'now()'::text，不是计算后的值。

4.3 从 MySQL 迁移时的列类型对照

如果你是从 MySQL 转过来的，下面的对应关系很常用：

这个表不需要背，遇到具体迁移需求时拿来查就行。

5. 索引、外键、序列、触发器：结构不止是表和列

很多人在这一步就停了，觉得"看完了表结构就算摸透了"。但实际上，表之间的血缘关系、索引的生效情况、序列的当前值，往往才是排查性能问题和数据不一致的关键。这部分建议也不要跳过。

5.1 索引清单与索引定义

查看索引有两类需求：一是"这个库有哪些索引"，二是"某张表上有哪些索引、是不是有效"。

查所有索引：

SELECT schemaname, tablename, indexname, indexdef FROM pg_indexes WHERE schemaname = 'public' ORDER BY tablename, indexname;

indexdef列会直接给出完整的 CREATE INDEX 语句，这是最清爽的查看方式，比你从系统表里拼出来要快得多。

那pg_index表和pg_indexes视图有什么区别？pg_index是基表，里面存了索引是否唯一（indisunique）、是否主键（indisprimary）、是否有效（indisvalid）等布尔标志。pg_indexes是外层的视图，把pg_index和pg_class等基础信息拼在了一起，适合日常直接查。

判断一个索引是否被查询计划生效，有个简单办法：

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'users';

然后到对应表上EXPLAIN SELECT ...，看执行计划里有没有走这个索引。比系统表里翻indisvalid更直接。

5.2 外键关系：谁引用了谁

PostgreSQL 里外键约束存储在pg_constraint表中，contype = 'f'表示 FOREIGN KEY。要查看某张表的外键以及被谁引用：

SELECT conname AS constraint_name, conrelid::regclass AS source_table, confrelid::regclass AS target_table, pg_get_constraintdef(oid) AS constraint_definition FROM pg_constraint WHERE contype = 'f' ORDER BY source_table;

有用的扩展是加一个WHERE conrelid = 'public.orders'::regclass来看这张表引用了谁；反向查谁引用了它，则把条件换成confrelid = 'public.orders'::regclass。

pg_get_constraintdef(oid)是一个很实用的函数，它能把约束的定义还原成可读的 SQL 描述，比如FOREIGN KEY (user_id) REFERENCES users(id)。如果你需要导出完整约束定义，写这一句就能拿到，不需要自己拼字段名。

5.3 序列和触发器：别忘了这两类隐藏对象

序列（sequence）在 PG 里是一等公民，很多自增主键依赖它。查看序列及其当前值：

\ds

对应的 SQL：

SELECT sequence_schema, sequence_name, start_value, increment_by, max_value FROM information_schema.sequences;

查看序列当前值可以用：

SELECT last_value, is_called FROM 序列名;

但注意，last_value是会话缓存的，并不是全局实时的，也可能因为缓存设置而跳号。它用来参考没问题，但不要假设它和下一次nextval()严格关系。

触发器用\dy查看，或者在information_schema.triggers里查询：

SELECT event_object_schema, event_object_table, trigger_name, action_timing, event_manipulation FROM information_schema.triggers;

对大多数日常查看需求，知道"有这些触发器、挂在哪张表上、什么时候触发"就够了。

6. 批量摸家底：统计行数、导出定义、迁移避坑

单表的查看命令到这里基本齐了。但工作中更常遇到的场景是：一个库里几十上百张表，我要一次性把所有表的基本信息拉出来，或者把整个库的结构导出来做基线存档。这一节就是干这个的。

6.1 一行脚本统计库里所有表的行数

前面提过，pg_stat_user_tables.n_live_tup是估算值，不是精确值。如果要用精确值，但又不想一张张COUNT(*)手动跑，可以用 DO 块或者\gexec技巧。

先看\gexec方案：

SELECT 'SELECT ' || quote_ident(schemaname) || '.' || quote_ident(tablename) || ', count(*) FROM ' || quote_ident(schemaname) || '.' || quote_ident(tablename) || ' GROUP BY 1;' FROM pg_tables WHERE schemaname = 'public';

在 psql 里执行后，它只会生成一串 SQL 文本。这时候在语句末尾加上\gexec：

SELECT 'SELECT ' || quote_ident(schemaname) || '.' || quote_ident(tablename) || ', count(*) FROM ' || quote_ident(schemaname) || '.' || quote_ident(tablename) || ' GROUP BY 1;' FROM pg_tables WHERE schemaname = 'public' \gexec

psql 会把生成出来的每条 SQL 依次执行，把结果拼成一张大结果集返回。这张表就是所有表的精确行数。quote_ident确保表名里的特殊字符被安全转义，防止注入或语法错误。这个在数据库课程设计、数据迁移核对行数时都非常好用。

6.2 定义导出：pg_dump 只拿表结构

如果要导出全库的表结构（DDL），首选不是自己拼 SQL，而是用 pg_dump：

pg_dump -h host -p 5432 -U username -d database_name -s -n public -f schema.sql

参数说明：

• -s是--schema-only，只导出对象定义，不包含数据。
• -n public指定只导出 public 这个 schema，避免把系统对象也带出来。
• -f schema.sql输出到文件。

导出的文件里不仅包含 CREATE TABLE，还包括索引、外键、序列、触发器等完整定义。这是最省心、最权威的方式。如果只想看一眼单表的创建语句，也可以使用：

SELECT pg_get_tabledef('public.users');

这个函数在较新的 PG 版本中可用，但不是所有环境都有，pg_dump 才是跨版本最稳妥的选择。

6.3 从 MySQL 迁移过来最容易踩的三个坑

最后说几个我在实际迁移和排查中反复遇到的点，都是血泪经验：

1. \dt查不到表，但表确实存在。先检查search_path，把SET search_path TO 目标模式, public;加上再查。这个问题 90% 是 schema 路径没指对。

2. 字段大小写问题。MySQL 里SELECT * FROM Users和select * from users效果一样，PG 完全不同。PG 会把没有引号的标识符强制转为小写，所以建表时用了"Users"，查询就必须写成"Users"，这可能是你在 PG 里反复报"relation does not exist"的最常见原因。

3. information_schema在 PG 里经常"不显示默认值"。比如information_schema.columns.column_default在某些约束下返回的是 NULL，而pg_attribute.attdefault里却有值。如果你在做结构对比工具，别只依赖 information_schema，混合查询 pg_catalog 更稳。

查看表结构这件事，看起来简单，但背后牵涉到 psql 元命令、系统目录、信息模式视图、统计信息、甚至 pg_dump 等多条路径。实际工作中，我自己的习惯是：交互式排查用 psql 元命令，写脚本和做监控用 pg_catalog 系列视图，跨数据库移植时用 information_schema 和 pg_dump 做交叉验证。三条路并不矛盾，反而能互补。

能把结构看清楚，你就能回答很多业务问题：这张表有哪些字段可以直接 join 上、那个字段默认值是不是符合预期、索引到底建没建上。这些问题的答案，往往就在上面这些命令里。