PostgreSQL - ALTER TABLE 命令

PostgreSQL 的 ALTER TABLE 命令用于更改现有表的定义。这个强大的命令允许你添加、删除或修改列，以及添加、移除或修改表约束。在 PostgreSQL 中，大多数 DDL（数据定义语言）命令，例如 ALTER TABLE，都是事务性的。这意味着你可以将它们封装在 BEGIN/COMMIT 块中，以便一次应用多个更改，或者在出现问题时使用 ROLLBACK 回滚。

注意：修改大型表有时会锁定该表，阻止读写操作。对于生产系统，请考虑使用专用工具或策略，例如针对每条语句的超时设置（SET lock_timeout），以实现零停机迁移。

修改列

以下是最常见的列相关操作。

1. 添加新列

ALTER TABLE table_name ADD COLUMN column_name datatype [column_constraint];

-- 示例：向 'employees' 表添加 'hire_date' 列
ALTER TABLE employees ADD COLUMN hire_date DATE;

2. 删除列

ALTER TABLE table_name DROP COLUMN [IF EXISTS] column_name;

-- 示例：删除 'gender' 列
ALTER TABLE employees DROP COLUMN IF EXISTS gender;

3. 重命名列

ALTER TABLE table_name RENAME COLUMN old_name TO new_name;

-- 示例：将 'salary' 重命名为 'annual_salary'
ALTER TABLE employees RENAME COLUMN salary TO annual_salary;

4. 更改列的数据类型

ALTER TABLE table_name ALTER COLUMN column_name TYPE new_datatype [USING expression];

-- 示例：将员工 ID 更改为更大的整型
ALTER TABLE employees ALTER COLUMN id TYPE BIGINT;

-- 示例（带类型转换）：将文本列更改为整型
ALTER TABLE employees ALTER COLUMN employee_code TYPE INT USING employee_code::integer;

5. 设置/移除默认值

-- 设置默认值
ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT default_value;

-- 移除默认值
ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

-- 示例：为新员工设置默认状态
ALTER TABLE employees ALTER COLUMN status SET DEFAULT 'active';

修改约束

约束强制执行数据完整性规则。它们可以随时添加或移除。

1. 添加 NOT NULL 约束

ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;

-- 示例：将 'name' 列设置为必填
ALTER TABLE employees ALTER COLUMN name SET NOT NULL;

2. 添加 UNIQUE 约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ...);

-- 示例：确保所有员工的电子邮件地址都是唯一的
ALTER TABLE employees ADD CONSTRAINT uq_employees_email UNIQUE (email);

3. 添加 PRIMARY KEY 约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name PRIMARY KEY (column1, ...);

-- 示例：为没有主键的表添加主键
ALTER TABLE departments ADD CONSTRAINT pk_departments PRIMARY KEY (id);

4. 添加 FOREIGN KEY 约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name FOREIGN KEY (column_name)
REFERENCES other_table (other_column_name);

-- 示例：将员工链接到其部门
ALTER TABLE employees ADD CONSTRAINT fk_employees_department
FOREIGN KEY (department_id) REFERENCES departments (id);

5. 添加 CHECK 约束

ALTER TABLE table_name ADD CONSTRAINT constraint_name CHECK (condition);

-- 示例：确保薪资始终为正数
ALTER TABLE employees ADD CONSTRAINT chk_salary_positive CHECK (annual_salary > 0);

6. 删除任意约束

ALTER TABLE table_name DROP CONSTRAINT [IF EXISTS] constraint_name;

-- 示例：移除唯一的电子邮件约束
ALTER TABLE employees DROP CONSTRAINT IF EXISTS uq_employees_email;