Postgresql 中文操作指南
5.6. Modifying Tables #
当您创建一个表并且意识到自己犯了一个错误，或者应用程序的要求发生了更改时，您可以删除表并再次创建它。但如果表中已填充了数据，或者表被其他数据库对象引用（例如外键约束），则这不是一个方便的选项。因此，PostgreSQL 提供了一系列命令来修改现有表。请注意，这在概念上不同于更改表中包含的数据：在这里我们有兴趣更改表的定义或结构。
When you create a table and you realize that you made a mistake, or the requirements of the application change, you can drop the table and create it again. But this is not a convenient option if the table is already filled with data, or if the table is referenced by other database objects (for instance a foreign key constraint). Therefore PostgreSQL provides a family of commands to make modifications to existing tables. Note that this is conceptually distinct from altering the data contained in the table: here we are interested in altering the definition, or structure, of the table.
您可以执行以下操作：
You can:
所有这些操作使用 ALTER TABLE 命令执行，其参考页面包含此处所给详细信息以外的详细信息。
All these actions are performed using the ALTER TABLE command, whose reference page contains details beyond those given here.
5.6.1. Adding a Column #
要添加一列，请使用如下命令：
To add a column, use a command like:
ALTER TABLE products ADD COLUMN description text;最初，新列会使用提供的任何默认值进行填充（如果您没有指定 DEFAULT 子句，则为 null）。
The new column is initially filled with whatever default value is given (null if you don’t specify a DEFAULT clause).
Tip
从 PostgreSQL 11 开始，添加具有常量默认值的一列不再意味着需要在执行 ALTER TABLE 语句时更新表的每一行。相反，将在下次访问行时返回默认值，并在重写表时应用，从而使 ALTER TABLE 即使在大型表上也能非常快。
From PostgreSQL 11, adding a column with a constant default value no longer means that each row of the table needs to be updated when the ALTER TABLE statement is executed. Instead, the default value will be returned the next time the row is accessed, and applied when the table is rewritten, making the ALTER TABLE very fast even on large tables.
但是，如果默认值不稳定（例如，clock_timestamp()），则需要使用在执行 ALTER TABLE 时计算的值更新每一行。为了避免潜在的冗长更新操作（尤其是如果您打算主要用非默认值填充列时），最好不添加默认值来添加列、使用 UPDATE 插入正确的值，然后按以下说明添加任何所需的默认值。
However, if the default value is volatile (e.g., clock_timestamp()) each row will need to be updated with the value calculated at the time ALTER TABLE is executed. To avoid a potentially lengthy update operation, particularly if you intend to fill the column with mostly nondefault values anyway, it may be preferable to add the column with no default, insert the correct values using UPDATE, and then add any desired default as described below.
您还可以使用常规语法同时定义列上的约束：
You can also define constraints on the column at the same time, using the usual syntax:
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');事实上，可以应用于 CREATE TABLE 中列描述的所有选项都可以用在这里。然而，请记住，默认值必须满足给定的约束，否则 ADD 将失败。或者，您也可以在正确填充新列后稍后添加约束（见下文）。
In fact all the options that can be applied to a column description in CREATE TABLE can be used here. Keep in mind however that the default value must satisfy the given constraints, or the ADD will fail. Alternatively, you can add constraints later (see below) after you’ve filled in the new column correctly.
5.6.2. Removing a Column #
要移除列，请使用如下命令：
To remove a column, use a command like:
ALTER TABLE products DROP COLUMN description;列中的任何数据都会消失。涉及该列的表约束也会被删除。但是，如果该列由其他表的外部键约束引用，PostgreSQL 不会静默地删除该约束。您可以通过添加 CASCADE 授权删除依赖于该列的所有内容：
Whatever data was in the column disappears. Table constraints involving the column are dropped, too. However, if the column is referenced by a foreign key constraint of another table, PostgreSQL will not silently drop that constraint. You can authorize dropping everything that depends on the column by adding CASCADE:
ALTER TABLE products DROP COLUMN description CASCADE;有关此机制背后的常规描述，请参见 Section 5.14。
See Section 5.14 for a description of the general mechanism behind this.
5.6.3. Adding a Constraint #
要添加约束，请使用表约束语法。例如：
To add a constraint, the table constraint syntax is used. For example:
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;要添加无法写为表约束的非空约束，请使用以下语法：
To add a not-null constraint, which cannot be written as a table constraint, use this syntax:
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;将立即检查约束，因此表数据必须满足约束才能将其添加进去。
The constraint will be checked immediately, so the table data must satisfy the constraint before it can be added.
5.6.4. Removing a Constraint #
要消除约束，您需要知道其名称。如果您给了它一个名称，那么就很容易。否则，系统会分配一个生成的名称，您需要了解它。psql 命令 \d _tablename_ 在此处可给予帮助；其他接口也可能提供一种检查表详细信息的方法。然后，命令为：
To remove a constraint you need to know its name. If you gave it a name then that’s easy. Otherwise the system assigned a generated name, which you need to find out. The psql command \d _tablename_ can be helpful here; other interfaces might also provide a way to inspect table details. Then the command is:
ALTER TABLE products DROP CONSTRAINT some_name;（如果您处理的是像 $2 这样的生成约束名称，请不要忘记您需要用双引号引起来使其成为有效的标识符。）
(If you are dealing with a generated constraint name like $2, don’t forget that you’ll need to double-quote it to make it a valid identifier.)
与删除列一样，如果您要删除别的什么东西所依赖的约束，则需要添加 CASCADE。一个例子是外部键约束依赖于引用列上的唯一或主键约束。
As with dropping a column, you need to add CASCADE if you want to drop a constraint that something else depends on. An example is that a foreign key constraint depends on a unique or primary key constraint on the referenced column(s).
这适用于除非空约束之外的所有约束类型。要删除非空约束，请使用：
This works the same for all constraint types except not-null constraints. To drop a not null constraint use:
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;（回想一下，非空约束没有名称。）
(Recall that not-null constraints do not have names.)
5.6.5. Changing a Column’s Default Value #
要为列设置新的默认值，请使用如下命令：
To set a new default for a column, use a command like:
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;请注意，这不会影响表中的任何现有行，它只会更改未来 INSERT 命令的默认值。
Note that this doesn’t affect any existing rows in the table, it just changes the default for future INSERT commands.
要移除任何默认值，请使用：
To remove any default value, use:
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;这实际上与将默认值设置为 null 相同。因此，删除尚未定义默认值不是错误，因为默认值隐式为 null 值。
This is effectively the same as setting the default to null. As a consequence, it is not an error to drop a default where one hadn’t been defined, because the default is implicitly the null value.
5.6.6. Changing a Column’s Data Type #
要将列转换为不同的数据类型，请使用诸如以下的命令：
To convert a column to a different data type, use a command like:
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);只有在列中的现有条目可以通过隐式转换转换为新类型时，此命令才会成功。如果需要更复杂的转换，您可以添加 USING 子句，该子句指定如何从旧值计算新值。
This will succeed only if each existing entry in the column can be converted to the new type by an implicit cast. If a more complex conversion is needed, you can add a USING clause that specifies how to compute the new values from the old.
PostgreSQL 将尝试将列的默认值（如果有）转换为新类型，以及涉及该列的所有约束。但这些转换可能会失败，或可能产生意外的结果。在更改列的类型之前，通常最好先删除列上的所有约束，然后在之后添加适当修改的约束。
PostgreSQL will attempt to convert the column’s default value (if any) to the new type, as well as any constraints that involve the column. But these conversions might fail, or might produce surprising results. It’s often best to drop any constraints on the column before altering its type, and then add back suitably modified constraints afterwards.
5.6.7. Renaming a Column #
要重命名列：
To rename a column:
ALTER TABLE products RENAME COLUMN product_no TO product_number;