9. 修改表结构（ALTER TABLE）

本节目标：学完能独立用 ALTER TABLE 给表加列、删列、改类型、重命名、加约束，并知道哪些操作有坑。

表建好之后，需求往往会变。比如要给 users 表加一个手机号字段，或者把 orders 的备注列删掉。这些事不用删表重建，用 ALTER TABLE 就能改。

ALTER TABLE 是「改已有表的结构」，不会动表里已有的数据行（除非你要改类型导致不兼容）。下面分几块讲。我之前带过新人，最常犯的错就是在大表上随手改结构把线上业务卡住，所以后半段我会专门说注意事项。

新增列（ADD COLUMN）

语法很简单，列会追加到表的最后面。PostgreSQL 不支持指定新列的位置，比如「插到第二列」这种操作做不到，新列永远在末尾。

ALTER TABLE users
ADD COLUMN phone VARCHAR(20);

一次加多列，写多个 ADD COLUMN 子句，用逗号隔开：

ALTER TABLE users
ADD COLUMN phone VARCHAR(20),
ADD COLUMN city VARCHAR(50);

加列时顺便给默认值也很常见，比如给 is_vip 默认设为 false：

ALTER TABLE users
ADD COLUMN is_vip BOOLEAN DEFAULT false;

Tip

从 PostgreSQL 11 起，给已有数据的表加一个「常量默认值」（比如 DEFAULT 0、DEFAULT 'pending'）是瞬间完成的，不会重写整张表。只有默认值里包含易变函数（如 now()）时才会逐行重写。所以放心加，不用太担心大表性能。

加 NOT NULL 列的坑

给已有数据的表加 NOT NULL 列会报错。因为旧行该列是 NULL，违反非空约束。正确做法是分三步：先加列（不带约束），把旧数据补上，再设 NOT NULL。

ALTER TABLE users ADD COLUMN age SMALLINT;
UPDATE users SET age = 18 WHERE age IS NULL;
ALTER TABLE users ALTER COLUMN age SET NOT NULL;

如果你加列时既写了 NOT NULL 又写了默认值，那可以一步到位，因为默认值会先填给所有旧行：

ALTER TABLE users ADD COLUMN is_vip BOOLEAN NOT NULL DEFAULT false;

删除列（DROP COLUMN）

删除列时，PostgreSQL 会顺手把引用该列的索引、约束一起删掉，不留垃圾。

ALTER TABLE users DROP COLUMN phone;

列不存在时会报错。加 IF EXISTS 只给提示、不报错，写迁移脚本时更稳：

ALTER TABLE users DROP COLUMN IF EXISTS phone;

一次删多列也用逗号隔开。如果别的对象（比如视图、外键）依赖这一列，要加 CASCADE 把依赖对象也一并删掉：

ALTER TABLE users DROP COLUMN phone CASCADE;

Warning

DROP COLUMN ... CASCADE 会把依赖这列的对象也删了，可能连带视图一起没。动手前用 \d 看一眼这张表被谁引用，确认能删再动手。

注意 DROP COLUMN 默认是 RESTRICT 行为：只要有依赖就拒绝删。加 CASCADE 才是强制连依赖一起删。删完列后，表占用的空间不会立刻返还给操作系统，需要 VACUUM 才能真正回收，不过对日常使用没影响。

修改列的数据类型

把某列从一种类型改成另一种，用 ALTER COLUMN ... TYPE。

ALTER TABLE users
ALTER COLUMN username TYPE VARCHAR(100);

TYPE 和 SET DATA TYPE 写法等价，挑顺手的用即可。

如果新旧类型不兼容（比如把字符串列改成数字列），需要加 USING 告诉它怎么转换：

ALTER TABLE orders
ALTER COLUMN amount TYPE NUMERIC(10,2)
USING amount::NUMERIC(10,2);

USING 后面跟一个表达式，把旧值转成新类型能接受的形式。比如有一张表用文本存性别，现在想改成枚举类型：

CREATE TYPE gender_enum AS ENUM ('男', '女');

CREATE TABLE profiles (
    id     INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    gender TEXT
);

ALTER TABLE profiles
ALTER COLUMN gender TYPE gender_enum
USING gender::gender_enum;

Note

改类型在大表上会重写整列数据，可能锁表一段时间。能预估数据量时，先在测试库跑一遍看看耗时。如果转换可能失败（比如文本里混进了非数字），先用 SELECT 筛一遍脏数据再改。

重命名列 / 重命名表

重命名列，COLUMN 关键字可省略：

ALTER TABLE users RENAME COLUMN email TO contact_email;

重命名列没有 IF EXISTS 选项，列不存在直接报错。但它有个贴心之处：依赖该列的视图、外键会被自动跟着改名，引用关系不会断。

重命名整张表，同样支持 IF EXISTS：

ALTER TABLE users RENAME TO members;
ALTER TABLE IF EXISTS users RENAME TO members;

重命名表也会自动更新引用它的外键和视图，不用手动改。

Warning

重命名表和列虽然方便，但要当心：已经有代码写死了旧列名、旧表名的地方会立刻报错。最好在改之前全局搜一遍应用代码，确认没有遗漏。特别是外键引用、ORM 映射、报表 SQL，都要同步改。

设置默认值与非空

给列加默认值、去默认值，以及开关非空约束，都用 ALTER COLUMN：

ALTER TABLE orders ALTER COLUMN status SET DEFAULT 'pending';
ALTER TABLE orders ALTER COLUMN status DROP DEFAULT;
ALTER TABLE users ALTER COLUMN age SET NOT NULL;
ALTER TABLE users ALTER COLUMN age DROP NOT NULL;

加默认值只影响之后插入的行，不会回填已存在的旧数据。这点容易误解——很多人以为 SET DEFAULT 会顺手把历史空值补上，其实不会。要回填得自己写 UPDATE。

增加与删除约束

除了列属性，还能直接加约束。比如给 email 加唯一约束（顺手起个名字方便以后删）：

ALTER TABLE users
ADD CONSTRAINT uk_users_email UNIQUE (email);

删除约束要用约束名：

ALTER TABLE users
DROP CONSTRAINT uk_users_email;

加 CHECK 约束时，如果表已有数据不满足，会失败。加 NOT VALID 可以先挂上约束、不校验旧数据，之后再用 VALIDATE CONSTRAINT 慢慢校验，避免长时间锁表：

ALTER TABLE users
ADD CONSTRAINT chk_age CHECK (age >= 0) NOT VALID;

ALTER TABLE users VALIDATE CONSTRAINT chk_age;

加外键也是同样套路：

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user
FOREIGN KEY (user_id) REFERENCES users(id);

Tip

大表加 CHECK 或 FOREIGN KEY 约束又不想长时间锁表，用 NOT VALID + 后台 VALIDATE 是常用技巧。先快速挂上约束保证新数据合规，再挑低峰期校验旧数据。

一个完整演练

假设 orders 表起初只有 id、user_id、amount。现在要补上「状态」和「备注」两列，并给状态加默认值：

-- 1. 加状态列，默认 pending
ALTER TABLE orders ADD COLUMN status VARCHAR(20) NOT NULL DEFAULT 'pending';

-- 2. 加备注列，可空
ALTER TABLE orders ADD COLUMN remark TEXT;

-- 3. 给金额加强约束，不能小于 0
ALTER TABLE orders ADD CONSTRAINT chk_amount CHECK (amount >= 0);

三步跑完，旧数据自动补上 pending，新结构立刻生效。

大表改结构的代价

不是所有 ALTER TABLE 都轻量。ADD COLUMN 加常量默认值、DROP COLUMN、SET DEFAULT 这类大多只改元数据，很快。但 ALTER COLUMN ... TYPE（改类型）要重写整列数据，ADD CONSTRAINT 之外给已有大表加 NOT NULL 也要扫全表，这些会拿表锁、阻塞读写。

一个经验：超过百万行的表，改结构尽量选低峰期，或先用 NOT VALID 挂约束再后台校验。PostgreSQL 有 CONCURRENTLY 用于建索引，但 ALTER TABLE 本身没有「并发改列」，只能靠分批或短事务降低影响。

Tip

评估一条 ALTER TABLE 重不重，看它是不是要重写数据或扫全表。只动元数据的操作可以放心做；要动数据的，先在测试库按真实数据量跑一次，计时后再上生产。

注意事项汇总

大表上改结构会锁表，数据多时可能卡住业务，先在测试库试，挑低峰期做。
DROP COLUMN ... CASCADE 会删掉依赖对象，动手前想清楚。
改类型尽量用 USING，避免转换失败；大表改类型会重写整列。
重命名列/表后，记得同步改应用代码里的旧名字。
SET DEFAULT 不回填历史数据，需要时自己 UPDATE。
自增列我们统一用 GENERATED ALWAYS AS IDENTITY，相关内容在第 15 章细讲。