18 ALTER TABLE 与 DROP

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

本章要点

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

示例

-- 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)。

运行示例

运行方式：

$ 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)

常见陷阱

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

下载

SQL 文件：alter-table-and-drop.sql · 全部示例文件 · 准备工作