修改表

当我们已经创建一个表并意识到犯了一个错误或者应用需求发生改变时，我们可以移除表并重新创建它。

但如果表里已经有数据或者被其他表引用时，这种做法就不合适了。

PostgreSQL 提供了一组命令对已有的表的定义或者表的结构进行修改。

增加列

sql
1ALTER TABLE products ADD COLUMN description text;

新列将被默认值所填充（如果没有指定DEFAULT子句，则会填充空值）。

从版本 11 开始，添加一个常量默认值不再意味着在执行 ALTER TABLE 语句时需要更新表的每一行。默认值将在下一次访问该行时返回，并且在表被重写时应用，从而使得 ALTER TABLE 即使在大表下也会执行得非常快。

但是，如果默认值是可变的，例如clock_timestamp()，则每一行需要被 ALTER TABLE 被执行时计算的值更新。

为了避免长时间的更新操作，最好添加没有默认值的列，再用 UPDATE 插入正确的值。

也可以同时为列定义约束，语法：

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

移除列

sql
1ALTER TABLE products DROP COLUMN description;

列中的数据将会消失。涉及到该列的表约束也会被移除。然而，如果该列被另一个表的外键所引用，PostgreSQL 不会安静地移除该约束。我们可以通过增加CASCADE来授权移除任何依赖于被删除列的所有东西：

sql
1ALTER TABLE products DROP COLUMN description CASCADE;

增加约束

可以使用表约束的语法来增加约束：

sql
1ALTER TABLE products ADD CHECK (name <> '');
2ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
3ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
4ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

约束会被立即检查，所以表中的数据必须在约束被增加前就符合约束。

移除约束

为了移除一个约束首先需要知道它的名称。如果在创建时没有给予指定名称，那约束的名称会由系统生成，我们需要找出这个名称。

查出名称的命令为：

sql
1SELECT
2   tc.constraint_name, tc.table_name, kcu.column_name,
3   ccu.table_name AS foreign_table_name,
4   ccu.column_name AS foreign_column_name,
5   tc.is_deferrable,tc.initially_deferred
6 FROM
7   information_schema.table_constraints AS tc
8   JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
9   JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
10 WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name = 'your table name';

解除约束的命令为：

sql
1ALTER TABLE products DROP CONSTRAINT some_name;

如果需要移除一个被某些别的东西依赖的约束，也需要加上CASCADE。一个例子是一个外键约束依赖于被引用列上的一个唯一或者主键约束。

但非空约束没有名字，所以移除非空约束可以用：

sql
1ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

更改默认值

sql
1ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

注意这不会影响任何表中已经存在的行，它只是为未来的INSERT命令改变了默认值。

移除默认值

sql
1ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

这等同于将默认值设置为空值。

修改列的数据类型

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

只有当列中的每一个项都能通过一个隐式造型转换为新的类型时该操作才能成功。如果需要一种更复杂的转换，应该加上一个USING子句来指定应该如何把旧值转换为新值。

PostgreSQL 将尝试把列的默认值转换为新类型，其他涉及到该列的任何约束也是一样。但是这些转换可能失败或者产生奇特的结果。因此最好在修改类型之前先删除该列上所有的约束，然后在修改完类型后重新加上相应修改过的约束。

重命名列

sql
1ALTER TABLE products RENAME COLUMN product_no TO product_number;

重命名表

sql
1ALTER TABLE products RENAME TO items;

小结

修改表指的是修改表的结构或者数据定义，常见的操作有：

增加列
删除列
增加约束
移除约束
更改默认值
移除默认值
修改列的数据类型
重命名列
重命名表