使用 PostgreSQL ALTER COLUMN 语句向表在添加列

在本文中，您将了解如何使用 PostgreSQL ALTER TABLE ... ALTER COLUMN 语句修改表中的现有列。

有时候，您需要修改一个已有的表中的一个已有的列。比如，修改列的名称，类型，约束，默认值等。

PostgreSQL ADD COLUMN 语法

要将新列添加到现有表，请按照如下语法使用 ALTER TABLE ... ADD COLUMN 语句：

ALTER TABLE table_name
ALTER [COLUMN] column_name alter_action
[, ALTER [COLUMN] ...];

解释说明：

  • table_name 是要在其中添加列的表。

  • ALTER [COLUMN] column_name alter_action 子句用来修改由列名 column_name 指定的列。其中 COLUMN 关键字是可以省略的。

  • alter_action 是修改动作，您可以使用以下动作之一：

    • 修改列的数据类型: [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]
    • 修改列的默认值：SET DEFAULT expression
    • 删除列的默认值：DROP DEFAULT
    • 设置或删除不能为 NULL: { SET | DROP } NOT NULL
    • 将生成列转为普通列: DROP EXPRESSION [ IF EXISTS ]
    • 修改列为标识列: ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]
    • 修改标识列的生成策略: { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]
    • 将标识列转为普通列: DROP IDENTITY [ IF EXISTS ]
    • 设置列的统计信息手机目标: SET STATISTICS integer
    • 设置属性选项: SET ( attribute_option = value [, ... ] )
    • 重置属性: RESET ( attribute_option [, ... ] )
    • 设置列的存储模式: SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    • 设置列的压缩方法: SET COMPRESSION compression_method

以下是几个常用的操作完整语法。

修改列类型

要修改一个列的数据类型，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ USING expression ]

说明：

  • 在 ALTER TABLE 关键字后指定要更改的列的表名。
  • 在 ALTER COLUMN 子句后指定要更改数据类型的列的名称。
  • 为 TYPE 关键字后的列提供新的数据类型。该 SET DATA TYPE 和 TYPE 是等价的。
  • PostgreSQL 允许您通过添加 USING 子句在修改数据类型时将列的值转换为新的值。

为列设置默认值

要修改一个列的默认值，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name SET DEFAULT expression

删除列默认值

要删除一个列的默认值，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name DROP DEFAULT

为列添加 NOT NULL

要为一个列添加 NOT NULL 约束，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name SET NOT NULL

删除列 NOT NULL

要从一个列删除 NOT NULL 约束，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name DROP NOT NULL

标识列

要修改一个列为标识列，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT }
      AS IDENTITY [ ( sequence_options ) ]

要将一个标识列修改为普通列，请使用以下语法：

ALTER TABLE table_name
  ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]

PostgreSQL 更改列类型示例

让我们创建一个新表命名 orders 并插入一些行。

CREATE TABLE orders (
    id serial PRIMARY KEY,
    order_no VARCHAR NOT NULL
);

INSERT INTO orders(order_no)
VALUES('10001'), ('10002');

要将 order_no 列的数据类型更改为 INT，请使用以下语句：

ALTER TABLE orders
ALTER COLUMN order_no TYPE INT;

PostgreSQL 发出了一个错误和一个非常有用的提示：

ERROR:  column "order_no" cannot be cast automatically to type integer
HINT:  You might need to specify "USING order_no::integer".

以下语句将 USING 子句添加到上述语句中：

ALTER TABLE orders
ALTER COLUMN order_no TYPE INT
USING order_no::integer;

修改成功。

结论

本文讨论了如何使用 PostgreSQL ALTER TABLE ... ALTER COLUMN 语句修改表中的现有列。