PostgreSQL 教程

PostgreSQL 修改数据表

修改数据表（Altering tables）是数据库管理中极其重要的一环。随着业务需求的变化，你往往需要调整现有的数据库结构，而不需要推倒重来重建整个表。

这包括添加新列、修改现有列的属性，以及移除不再需要的列。

1. 向表中添加列 (Adding Columns)

当需要存储新的信息时，向现有表中添加列是最常见的操作。添加列的基础语法如下：

ALTER TABLE table_name
ADD COLUMN column_name data_type constraint;
  • ALTER TABLE: 这是一个命令关键字，声明你准备修改某个表。
  • table_name: 你想要修改的表名。
  • ADD COLUMN: 声明你要执行的操作是“添加新列”。
  • column_name: 新列的名称。
  • data_type: 新列的数据类型（例如 INTEGER, VARCHAR, DATE 等）。
  • constraint: （可选）你可以直接为新列添加约束，比如 NOT NULL 或 UNIQUE。

1.1 基础添加示例

假设我们有一个 employees（员工）表，现在我们想增加一列来存储员工的电子邮件地址。

ALTER TABLE employees
ADD COLUMN email VARCHAR(100);

这条语句向 employees 表中添加了一个名为 email 的列，数据类型为最大长度 100 的字符串。

1.2 添加带有默认值的列

有时你希望新添加的列对所有已存在的旧数据行都有一个初始值，这时可以使用默认值 (DEFAULT)。

ALTER TABLE employees
ADD COLUMN hire_date DATE DEFAULT CURRENT_DATE;

这添加了一个名为 hire_date (入职日期) 的 DATE 类型列。DEFAULT CURRENT_DATE 意味着，对于表中已存在的老员工，或者未来插入新员工但没填写日期时，系统会自动填入当天的日期。

1.3 添加带有约束的列

你也可以在添加列的同时设定数据校验规则（约束）。

ALTER TABLE employees
ADD COLUMN salary DECIMAL(10, 2) CHECK (salary >= 0);

这里我们添加了一个 salary (薪水) 列。DECIMAL(10, 2) 适合存储货币。CHECK (salary >= 0) 是一个检查约束，强制保证薪水绝对不能是负数。

2. 修改表中的列 (Modifying Columns)

修改列允许你更改现有列的数据类型、默认值或约束条件。通用语法结构如下：

ALTER TABLE table_name
ALTER COLUMN column_name [操作指令];

常见的操作指令包括：

  • TYPE data_type: 更改数据类型。
  • SET DEFAULT default_value: 设置新的默认值。
  • DROP DEFAULT: 移除现有的默认值。
  • SET NOT NULL / DROP NOT NULL: 添加或移除非空约束。

2.1 修改数据类型

假设你最初将 employees 表的 phone_number 定义为了整数 (INTEGER)，但现在你发现需要存储包含国际区号（如 "+86"）的号码，因此必须将其改为字符串 (VARCHAR)。

ALTER TABLE employees
ALTER COLUMN phone_number TYPE VARCHAR(20);

重要提示： 当你更改数据类型时，PostgreSQL 会尝试将现有的旧数据转换成新类型。如果转换在逻辑上行不通（例如，你想把包含字母 "ABC" 的字符串列强制改成 INTEGER），ALTER TABLE 命令将会报错失败。

2.2 设置和移除默认值

为现有的 email 列设置一个默认值：

ALTER TABLE employees
ALTER COLUMN email SET DEFAULT 'no_email@provided.com';

如果你后来觉得不需要这个默认值了，可以将其移除：

ALTER TABLE employees
ALTER COLUMN email DROP DEFAULT;

2.3 添加和移除非空约束 (NOT NULL)

要求 email 列以后必须填写（不能为空）：

ALTER TABLE employees
ALTER COLUMN email SET NOT NULL;

要移除这个非空限制：

ALTER TABLE employees
ALTER COLUMN email DROP NOT NULL;

3. 从表中删除列 (Dropping Columns)

删除列会将其从表中彻底移除。此操作必须极其谨慎，因为它会导致数据永久丢失。

语法如下：

ALTER TABLE table_name
DROP COLUMN column_name;

3.1 基础删除示例

如果你决定不再需要 employees 表中的 phone_number 列：

ALTER TABLE employees
DROP COLUMN phone_number;

核心注意事项：

  1. 删除列会永久销毁该列下存储的所有数据。
  2. 如果数据库中有其他对象（如视图 Views、函数 Functions 或存储过程）依赖于你要删除的列，PostgreSQL 通常会报错阻止你删除。你需要先修改或删除那些依赖它的对象。
  3. 在生产环境中，执行 DROP COLUMN 前进行数据库备份是极为良好的习惯。

3.2 使用 IF EXISTS 安全删除

为了避免因为尝试删除一个根本不存在的列而导致脚本报错中断，你可以使用 IF EXISTS 子句：

ALTER TABLE employees
DROP COLUMN IF EXISTS phone_number;

如果 phone_number 存在，它会被删除；如果不存在，命令也会顺利执行完毕，不会抛出错误。

4. 真实业务场景演练

让我们看看在不同的实际业务中，表结构是如何演进的。

场景一：电商平台 (E-commerce Platform)

  • 添加列： 随着业务扩展到海外，你需要记录订单的结算货币。
ALTER TABLE orders ADD COLUMN currency VARCHAR(3);
  • 修改列： 发现原始的 order_status (订单状态) 是随意填写的文本 (TEXT)，现在需要将其收紧，变更为具有严格字数限制的格式。
ALTER TABLE orders ALTER COLUMN order_status TYPE VARCHAR(20);
  • 删除列： 接入了第三方专业的物流系统后，原订单表里简单的 shipping_address (送货地址) 列变得多余，将其清理。
ALTER TABLE orders DROP COLUMN shipping_address;

场景二：社交媒体应用 (Social Media App)

  • 添加列： 为了分析用户活跃度，需要在 users 表中增加“最后登录时间”。
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
  • 修改列： 出于安全合规，决定延长用户名的最大允许长度。
ALTER TABLE users ALTER COLUMN username TYPE VARCHAR(100);
  • 删除列： 上线了全新的、颗粒度更细的隐私设置面板，旧版粗放的 profile_visibility (主页可见性) 字段正式退役。
ALTER TABLE users DROP COLUMN profile_visibility;