PostgreSQL 教程

Appearance

5.6. 修改表# ​

5.6.1. 添加列
5.6.2. 删除列
5.6.3. 添加约束
5.6.4. 删除约束
5.6.5. 更改列的默认值
5.6.6. 更改列的数据类型
5.6.7. 重命名列
5.6.8. 重命名表

当您创建一个表并意识到自己犯了一个错误，或者应用程序的要求发生了改变，您可以删除该表并重新创建它。但是，如果该表已经填充了数据，或者该表被其他数据库对象（例如外键约束）引用，则这不是一个方便的选择。因此，PostgreSQL提供了一系列命令来对现有表进行修改。请注意，这在概念上不同于更改表中包含的数据：这里我们感兴趣的是更改表的定义或结构。

您可以

  • 添加列

  • 删除列

  • 添加约束

  • 删除约束

  • 更改默认值

  • 更改列数据类型

  • 重命名列

  • 重命名表

所有这些操作都是使用ALTER TABLE命令执行的，其参考页面包含此处未提供的详细信息。

5.6.1. 添加列# ​

要添加列，请使用类似以下内容的命令

ALTER TABLE products ADD COLUMN description text;

新列最初填充了给定的任何默认值（如果您未指定DEFAULT子句，则为 null）。

提示

从PostgreSQL11开始，添加具有常量默认值的新列不再意味着在执行ALTER TABLE语句时需要更新表中的每一行。相反，将在下次访问该行时返回默认值，并在重写表时应用该默认值，即使在大型表上，也能使ALTER TABLE非常快速。但是，如果默认值不稳定（例如，clock_timestamp()），则需要使用在执行ALTER TABLE时计算的值来更新每一行。为了避免可能很长的更新操作，特别是如果您打算用大部分非默认值填充该列，最好不添加默认值，使用UPDATE插入正确的值，然后按如下所述添加任何所需的默认值。

您还可以使用通常的语法同时定义列上的约束

ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

事实上，可以在CREATE TABLE中应用于列描述的所有选项都可以在这里使用。但是请记住，默认值必须满足给定的约束，否则ADD将失败。或者，您可以在正确填充新列后稍后添加约束（请参见下文）。

5.6.2. 删除列# ​

要删除列，请使用类似以下内容的命令

ALTER TABLE products DROP COLUMN description;

列中的任何数据都将消失。涉及该列的表约束也将被删除。但是，如果该列被另一张表的外部键约束引用，PostgreSQL将不会静默地删除该约束。您可以通过添加CASCADE来授权删除所有依赖于该列的内容

ALTER TABLE products DROP COLUMN description CASCADE;

有关此机制背后的通用机制的说明，请参见第 5.14 节。

5.6.3. 添加约束# ​

要添加约束，请使用表约束语法。例如

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;

约束将立即被检查，因此表数据必须满足约束才能被添加。

5.6.4. 删除约束# ​

要删除约束，您需要知道其名称。如果您给它命名，那就很容易。否则，系统会分配一个生成名称，您需要找出它。psql命令\d*tablename*在这里可能会有帮助；其他界面也可能提供一种检查表详细信息的方法。然后命令是

ALTER TABLE products DROP CONSTRAINT some_name;

（如果您正在处理一个生成的约束名称，如$2，请不要忘记您需要用双引号将其括起来以使其成为一个有效的标识符。）

与删除列一样，如果您要删除其他内容所依赖的约束，则需要添加CASCADE。一个示例是外键约束依赖于所引用列上的唯一或主键约束。

这适用于除非空约束之外的所有约束类型。要删除非空约束，请使用

ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

（请记住，非空约束没有名称。）

5.6.5. 更改列的默认值# ​

要为列设置新的默认值，请使用类似以下的命令

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

请注意，这不会影响表中的任何现有行，它只会更改未来INSERT命令的默认值。

要删除任何默认值，请使用

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

这实际上与将默认值设置为 null 相同。因此，删除尚未定义默认值并不是一个错误，因为默认值隐式为 null 值。

5.6.6. 更改列的数据类型# ​

要将列转换为不同的数据类型，请使用类似以下的命令

ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

只有当列中的每个现有条目都可以通过隐式转换转换为新类型时，此操作才成功。如果需要更复杂的转换，您可以添加一个USING子句，该子句指定如何从旧值计算新值。

PostgreSQL将尝试将列的默认值（如果有）转换为新类型，以及涉及该列的任何约束。但这些转换可能会失败，或者可能会产生令人惊讶的结果。在更改列的类型之前，通常最好删除列上的任何约束，然后在之后添加适当修改的约束。

5.6.7. 重命名列# ​

要重命名列

ALTER TABLE products RENAME COLUMN product_no TO product_number;

5.6.8. 重命名表# ​

要重命名表

ALTER TABLE products RENAME TO items;