PostgreSQL修改表结构

TrumanWong
7/2/2024
TrumanWong

当您创建表并意识到自己犯了一个错误,或者应用程序的要求发生了变化时,您可以删除该表并重新创建它。但是,如果表中已经有了数据,或者该表被其他数据库对象(例如外键约束)引用,那么这不是一个方便的选择。因此,PostgreSQL提供了一组命令来修改现有表。

添加列

ALTER TABLE products ADD COLUMN description text;

你也可以在添加列的时候定义约束:

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

删除列

ALTER TABLE products DROP COLUMN description;

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

ALTER TABLE products DROP COLUMN description CASCADE;

添加约束

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;

更改列的默认值

ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

请注意,这不会影响表中的任何现有行,它只会更改以后执行INSERT命令时的默认值。

删除默认值:

ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

这实际上与将默认值设置为 null 相同。

更改列的数据类型

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

只有被更改的列中的所有值都可以通过隐式转换转换为新类型时,此操作才会成功。

PostgreSQL将尝试将列的默认值(如果有)以及涉及该列的任何约束转换为新类型。但这些转换可能会失败,或可能产生意想不到的结果。通常最好在更改列类型之前删除列上的所有约束,然后在之后添加适当修改的约束。

重命名列

ALTER TABLE products RENAME COLUMN product_no TO product_number;

重命名表

ALTER TABLE products RENAME TO items;