ALTER TABLE 用来修改已经存在的表。最常用的形式是 ADD COLUMN、RENAME COLUMN ... TO、DROP COLUMN 和 ALTER COLUMN ... TYPE。一条语句里可以放多个操作,用逗号分隔。
已有的行在 plan 中得到 'free',在 signed_up 中得到 NULL。从 PostgreSQL 11 开始,添加带常量默认值的列不会重写表,所以即使是大表也很快。像 clock_timestamp() 这样的易变默认值仍然会重写每一行。
重命名列或表
重命名是即时完成的,数据、索引和约束都会保留。视图仍然能用,因为它们引用的是表的内部 id,而不是表名。在其他地方以文本形式写下的名字不会跟着变:PL/pgSQL 函数体和应用中的查询仍然使用旧名字,所以重命名之前先搜一下。
删除列
DROP COLUMN 删除列及其数据。如果有视图或外键依赖这一列,就会失败。CASCADE 会把它们一起删除,IF EXISTS 在列已经不存在时跳过报错。
这里 PostgreSQL 不会重写表。它只是把这一列标记为已删除,空间会随着行的更新或者表被重写时回收。
修改列的类型
ALTER COLUMN ... TYPE 会转换每一个值。如果旧类型到新类型之间没有自动转换,就加上 USING,给出计算新值的表达式:
没有 USING 的话,这条语句会报错 column "price" cannot be cast automatically to type numeric。只要有一个值无法转换,整条语句就会失败,表保持不变。
大多数类型修改都会重写表。也有不会的:把 varchar(n) 改长,或者把 varchar 改成 text。
设置或删除默认值和 NOT NULL
SET NOT NULL 会扫描整张表,只要有一行是 NULL 就会失败,所以先把空缺填上:
DROP DEFAULT 和 DROP NOT NULL 用来撤销它们。新的默认值只对以后的插入生效,已有的行保持原值。
添加约束
ALTER TABLE orders ADD CONSTRAINT total_positive CHECK (total > 0);
ALTER TABLE orders DROP CONSTRAINT total_positive;
添加 CHECK 或外键时会扫描整张表来验证。对于又大又繁忙的表,先加上 NOT VALID,这样只检查新行。然后运行 ALTER TABLE orders VALIDATE CONSTRAINT total_positive;。它在较弱的锁下检查旧行,读写可以继续进行。外键页面用的是同样的做法,ALTER TABLE 参考文档列出了每种形式和它需要的锁。
常见问题
PostgreSQL 能调整列的顺序吗?
不能。ALTER TABLE 无法移动列,新列总是加在最后。在 SELECT 中按你想要的顺序列出列,或者按那个顺序新建一张表再把数据复制过去。
PostgreSQL 的 ALTER TABLE 会锁表吗?
大多数形式会获取 ACCESS EXCLUSIVE 锁,在语句完成之前阻塞读和写。快速的修改(重命名、删除列、添加带常量默认值的列)只会短暂持有这个锁。需要重写表的类型修改会在整个重写过程中一直持有它。
PostgreSQL 怎么一次添加多列?
在一条语句中用逗号分隔多个操作:ALTER TABLE users ADD COLUMN phone text, ADD COLUMN city text;。同一条 ALTER TABLE 里的所有操作一起执行,只加一次锁。
PostgreSQL 的 ALTER TABLE 能回滚吗?
能。PostgreSQL 的 DDL 是事务性的,所以 BEGIN ... ROLLBACK 中的 ALTER TABLE 会被完全撤销。