PostgreSQL / 系统课时
查看表结构
查看表结构
本课解决“数据库里实际生效了什么结构”的问题。你将分别使用 psql、information_schema 和 PostgreSQL 系统目录查看列、类型、默认值与约束,并知道三种方式各自适合什么场景。
学习目标
- 使用 psql 快速查看表结构与附加信息
- 用标准视图查询列定义
- 用系统目录提取完整约束定义
用 psql 快速检查
在 psql 中,\d 是客户端元命令,不是 SQL。它适合开发和排障时快速查看表、索引、约束与存储信息。
\d+ public.products预期结果:
Table "public.products"
Column | Type | Nullable | Default
--------------+--------------------------+----------+-----------------------------------
product_id | bigint | not null | generated always as identity
product_code | character varying(32) | not null |
product_name | character varying(100) | not null |
price | numeric(12,2) | not null |
status | character varying(16) | not null | 'ACTIVE'::character varying
created_at | timestamp with time zone | not null | CURRENT_TIMESTAMP
Indexes:
"<PRIMARY_KEY_NAME>" PRIMARY KEY, btree (product_id)
"<UNIQUE_INDEX_NAME>" UNIQUE CONSTRAINT, btree (product_code)
Check constraints:
"<CHECK_NAME>" CHECK (price >= 0::numeric)约束名、类型显示和列宽可能因版本、建表语句及终端设置不同。\d+ 比 \d 多显示存储、统计目标或访问方法等附加信息。要确认当前有哪些关系,可使用:
\dt public.*预期结果:
Schema | Name | Type | Owner
--------+----------+-------+--------
public | products | table | <OWNER>
(1 row)用 information_schema 查询列
information_schema 是 SQL 标准定义的一组元数据视图,适合应用程序、脚本和跨数据库产品的通用查询。
SELECT
ordinal_position,
column_name,
data_type,
character_maximum_length,
numeric_precision,
numeric_scale,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'products'
ORDER BY ordinal_position;预期结果:
ordinal_position | column_name | data_type | is_nullable
------------------+--------------+--------------------------+------------
1 | product_id | bigint | NO
2 | product_code | character varying | NO
3 | product_name | character varying | NO
4 | price | numeric | NO
5 | status | character varying | NO
6 | created_at | timestamp with time zone | NO
(6 rows)实际查询还会返回长度、精度、默认值等列,这里只展示关键字段。信息模式通常只显示当前用户有权访问的对象,因此“查不到”不一定代表对象不存在,也可能是权限不足。
用系统目录查看约束
PostgreSQL 系统目录提供更完整的产品特有信息。查询约束时,可结合 pg_constraint 与 pg_get_constraintdef 获得可读定义。
SELECT
con.conname AS constraint_name,
con.contype AS constraint_type,
pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint AS con
JOIN pg_class AS rel
ON rel.oid = con.conrelid
JOIN pg_namespace AS nsp
ON nsp.oid = rel.relnamespace
WHERE nsp.nspname = 'public'
AND rel.relname = 'products'
ORDER BY con.conname;预期结果:
constraint_name | constraint_type | definition
---------------------+-----------------+------------------------------------
<CHECK_NAME> | c | CHECK ((price >= (0)::numeric))
<PRIMARY_KEY_NAME> | p | PRIMARY KEY (product_id)
<UNIQUE_NAME> | u | UNIQUE (product_code)contype 中常见的 p、u、f、c 分别代表主键、唯一、外键和检查约束。系统目录是 PostgreSQL 专有接口,字段更丰富,但升级或跨数据库迁移时需要更谨慎。
选择合适的查看方式
人工检查优先使用 \d+,因为信息集中且读取速度快。自动化脚本若只需要列名、类型和可空性,优先使用 information_schema.columns。需要表达式索引、约束内部定义、对象 OID 等 PostgreSQL 细节时,再查询系统目录。
不要直接修改 pg_catalog 中的目录表。结构变更应使用 CREATE、ALTER、DROP 等 DDL,让 PostgreSQL 维护依赖关系和一致性。