# ALTER TABLE 与 DROP

> 修改表和约束，同时理解重写和加锁的代价。

---

LLMS 索引： [llms.txt](/llms.txt)

---

表会不断演化。`ALTER TABLE` 用于修改已有表：添加或删除列，重命名列或表本身，添加或删除约束，修改列的类型，修改列的默认值。`DROP TABLE` 会删除表及其包含的每一行。PostgreSQL 中的模式变更具有事务性——`ALTER TABLE` 失败时会正常回滚，你还可以把多个 `ALTER` 放进 `BEGIN ... COMMIT`，以原子方式应用它们。

## 本章要点 {#learn}

- 添加、重命名和删除列
- 添加和删除约束
- 使用 `USING` 修改列的类型
- 重命名表
- 常见修改操作对锁和表重写的影响

## 示例 {#example}

```sql {num="18.1" caption="ALTER TABLE 与 DROP"}
-- ADD COLUMN: new columns appear at the end
ALTER TABLE products ADD COLUMN description text;

-- ADD COLUMN with a default (cheap in PG 11+ for constant defaults)
ALTER TABLE products ADD COLUMN sku text NOT NULL DEFAULT '';

-- Rename a column
ALTER TABLE products RENAME COLUMN description TO short_desc;

-- Change a column type (use USING when a cast is needed)
ALTER TABLE products ALTER COLUMN sku TYPE varchar(40);

-- Add a constraint after the fact
ALTER TABLE products ADD CONSTRAINT sku_format CHECK (sku ~ '^[A-Z0-9-]*$');

-- Drop a column
ALTER TABLE products DROP COLUMN short_desc;

-- Rename the table itself
ALTER TABLE products RENAME TO products_old;
ALTER TABLE products_old RENAME TO products;

-- DROP TABLE removes the table and all data
-- (commented out so the rest of the lessons can still use products)
-- DROP TABLE products;

SELECT tablename FROM pg_tables WHERE tablename = 'products';
```

每条 `ALTER TABLE` 都是一个独立的事务性语句。你也可以批量执行多个操作：`ALTER TABLE t ADD COLUMN a int, ADD COLUMN b text, DROP COLUMN c;`——把它们放在一条语句中运行会更快，因为表只需被访问一次。

有两种操作值得从代价角度理解：

- **添加没有默认值（或默认值为常量）的列很快**——PostgreSQL 只需记录新列的元数据。
- **许多列类型变更都会重写表。** `ALTER COLUMN x TYPE bigint` 会读取并重写每一行。在大表上，这可能耗时数小时，并在整个过程中持有排他锁。有些二进制兼容的变更可以避免重写。对于重写量大的变更，应规划如下迁移：添加新列 → 回填 → 切换；`pg_repack` 不能执行任意类型转换。

`ALTER TABLE ... ALTER COLUMN col TYPE new_type USING expr` 允许你指定如何转换现有值：`ALTER COLUMN price TYPE numeric(12,2) USING price::numeric(12,2)`。

## 运行示例 {#run}

运行方式：

```text
$ psql -f source/alter-table-and-drop.sql postgres
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
 tablename
-----------
 products
(1 row)
```

## 常见陷阱 {#pitfalls}

- 向现有列添加 `NOT NULL` 时，要求该列中已经没有任何空值，并且会获取 `ACCESS EXCLUSIVE` 锁，一直持有到验证完成。先回填，再 `SET NOT NULL`。从 PostgreSQL 12 开始，如果存在匹配的 `CHECK` 约束，`ALTER COLUMN x SET NOT NULL` 可以更快完成。
- 删除列不会立即回收磁盘空间——该列只会被标记为无效。运行 `VACUUM FULL`（重量级操作）或重新创建表，才能回收空间。
- 重命名列会更新由系统目录跟踪的依赖关系，例如视图，但可能破坏应用程序 SQL 和以文本形式存储的函数体。重命名之前请检查这些用法。
- 对 `ENUM` 执行 `ALTER TYPE` 时，不能移除已有值；添加值则没有问题。

> [!TIP] 提示
> 对于零停机的模式变更，标准做法是：添加新列 → 分批回填 → 让应用同时写入两列 → 切换读操作 → 删除旧列。Liquibase、Flyway 或 `pg_repack` 等工具可以提供帮助；数据库负责加锁，你负责编排各个步骤。

> [!NOTE] 动手练习
> 先加回一列：`ALTER TABLE products ADD COLUMN notes text;`。然后重命名：`ALTER TABLE products RENAME COLUMN notes TO remarks;`。再删除：`ALTER TABLE products DROP COLUMN remarks;`。在 psql 中用 `\d products` 检查。

## 下载 {#downloads}

SQL 文件：[alter-table-and-drop.sql](/examples/source/alter-table-and-drop.sql) · [全部示例文件](/examples/examples.zip) · [准备工作](/#prerequisites)