PostgreSQL / 系统课时

查看表结构

查看表结构

本课解决“数据库里实际生效了什么结构”的问题。你将分别使用 psql、information_schema 和 PostgreSQL 系统目录查看列、类型、默认值与约束,并知道三种方式各自适合什么场景。

学习目标

  • 使用 psql 快速查看表结构与附加信息
  • 用标准视图查询列定义
  • 用系统目录提取完整约束定义

用 psql 快速检查

在 psql 中,\d 是客户端元命令,不是 SQL。它适合开发和排障时快速查看表、索引、约束与存储信息。

text
\d+ public.products

预期结果:

text
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 多显示存储、统计目标或访问方法等附加信息。要确认当前有哪些关系,可使用:

text
\dt public.*

预期结果:

text
 Schema |   Name   | Type  | Owner
--------+----------+-------+--------
 public | products | table | <OWNER>
(1 row)

用 information_schema 查询列

information_schema 是 SQL 标准定义的一组元数据视图,适合应用程序、脚本和跨数据库产品的通用查询。

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;

预期结果:

text
 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 获得可读定义。

sql
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;

预期结果:

text
   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 维护依赖关系和一致性。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

查看表结构

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通