当您创建表后意识到自己犯了错误,或者应用程序的需求发生变化时,您可以删除表然后重新创建它。但如果表中已经填充了数据,或者该表被其他数据库对象(例如外键约束)引用,则此选项不方便。因此,PostgreSQL 提供了一系列命令来修改现有表。请注意,这在概念上不同于修改表中的数据:这里我们关注的是修改表的定义或结构。
您可以
添加列
删除列
添加约束
删除约束
更改默认值
更改列数据类型
重命名列
重命名表
所有这些操作都使用 ALTER TABLE 命令执行,其参考页包含了比这里给出的更详细的信息。
要添加列,请使用类似如下的命令:
ALTER TABLE products ADD COLUMN description text;
新列最初会填充任何给定的默认值(如果您不指定 DEFAULT
子句,则为 null)。
添加具有常量默认值的列不需要在执行 ALTER TABLE
语句时更新表中的每一行。相反,当访问行时,将返回默认值,并在重写表时应用该值,这使得 ALTER TABLE
即使在大型表上也非常快速。
如果默认值是易失的(例如 clock_timestamp()
),则在执行 ALTER TABLE
时,每一行都需要使用计算出的值进行更新。为了避免潜在的长时间更新操作,特别是如果您打算用非默认值填充该列,最好添加一个没有默认值的列,使用 UPDATE
插入正确的值,然后按照下面的描述添加任何所需的默认值。
您也可以同时在此列上定义约束,使用常规语法:
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');
实际上,可以在此处使用 CREATE TABLE
中应用于列描述的所有选项。但请记住,默认值必须满足给定的约束,否则 ADD
将失败。或者,您可以在填充新列后(参见下文)稍后添加约束。
要删除列,请使用类似如下的命令:
ALTER TABLE products DROP COLUMN description;
列中的任何数据都会消失。涉及该列的表约束也会被删除。但是,如果该列被另一表的 外键约束 引用,PostgreSQL 不会静默删除该约束。您可以通过添加 CASCADE
来授权删除依赖于该列的所有内容。
ALTER TABLE products DROP COLUMN description CASCADE;
有关此机制的一般描述,请参阅 第 5.15 节。
要添加约束,请使用表约束语法。例如:
ALTER TABLE products ADD CHECK (name <> ''); ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no); ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
要添加一个非空约束(通常不写成表约束),可以使用此特殊语法:
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;
如果该列已经有一个非空约束,此命令将静默地什么也不做。
约束将立即检查,因此表数据必须满足约束才能添加。
要删除约束,您需要知道它的名称。如果您给它命名了,那就很容易。否则,系统会分配一个生成的名称,您需要找出它。 psql 命令 \d
在这里可能很有帮助;其他接口也可能提供检查表详细信息的方法。然后命令是:tablename
ALTER TABLE products DROP CONSTRAINT some_name;
与删除列一样,如果您想删除被其他对象依赖的约束,则需要添加 CASCADE
。例如,外键约束依赖于被引用列的唯一或主键约束。
可以使用简化的语法删除非空约束:
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;
这与添加非空约束的 SET NOT NULL
语法相对应。如果该列没有非空约束,此命令将静默地什么也不做。(请记住,一个列最多只能有一个非空约束,因此这个命令作用于哪个约束永远不会有歧义。)
要为列设置新的默认值,请使用类似如下的命令:
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;
请注意,这不会影响表中任何现有行,它只会改变未来 INSERT
命令的默认值。
要删除任何默认值,请使用:
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;
这实际上与将默认值设置为 null 相同。因此,删除一个尚未定义默认值的默认值并不算错误,因为默认值隐式为 null。
要将列转换为不同的数据类型,请使用类似如下的命令:
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);
只有当列中的每个现有条目都可以通过隐式转换转换为新类型时,此操作才会成功。如果需要更复杂的转换,您可以添加一个 USING
子句,指定如何从旧值计算新值。
PostgreSQL 将尝试将列的默认值(如果有)转换为新类型,以及任何涉及该列的约束。但这些转换可能会失败,或产生意外的结果。通常最好在更改列类型之前删除该列上的任何约束,然后稍后再添加适当修改的约束。
如果您在文档中发现任何不正确、与您对特定功能的体验不符或需要进一步澄清的内容,请使用 此表单 报告文档问题。