PostgreSQL ALTER TABLE 命令

作者likuolei

下面是一份 PostgreSQL ALTER TABLE 命令 的完整、系统化参考手册，涵盖 所有常用子句、语法结构、实战示例 和 最佳实践，适合开发、DBA 和架构师使用。

一、基本语法概览

ALTER TABLE [IF EXISTS] table_name
    action1 [, action2, ...];

多个操作可以 用逗号分隔，一次性执行，事务原子性。

二、所有 action 子句分类

分类子句说明
列操作ADD COLUMN添加列
DROP COLUMN删除列
ALTER COLUMN修改列属性
RENAME COLUMN重命名列
约束操作ADD CONSTRAINT添加约束
DROP CONSTRAINT删除约束
表属性RENAME TO重命名表
SET SCHEMA移动到新 schema
OWNER TO更改所有者
TABLESPACE更改表空间
存储参数SET设置表级参数（如 fillfactor）
RESET重置参数
分区ATTACH PARTITION附加分区
DETACH PARTITION分离分区
复制与继承ENABLE/DISABLE ROW LEVEL SECURITY行级安全
INHERIT / NO INHERIT继承控制

三、详细用法与示例

1. 添加列 ADD COLUMN

-- 基本添加
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT NOW();

-- 可空 + 默认值
ALTER TABLE users ADD COLUMN is_active BOOLEAN DEFAULT TRUE;

-- 带 CHECK 约束
ALTER TABLE users ADD COLUMN age INT CHECK (age >= 0 AND age <= 150);

-- 带注释
ALTER TABLE users ADD COLUMN phone VARCHAR(20),
                  ADD COLUMN source TEXT;
COMMENT ON COLUMN users.phone IS '用户手机号';

2. 删除列 DROP COLUMN

-- 基本删除
ALTER TABLE users DROP COLUMN temp_col;

-- 级联删除（慎用！）
ALTER TABLE users DROP COLUMN temp_col CASCADE;

-- 安全删除（存在才删）
ALTER TABLE IF EXISTS users DROP COLUMN IF EXISTS temp_col;

3. 修改列 ALTER COLUMN

操作语法
设置/取消默认值ALTER COLUMN col SET DEFAULT expr
ALTER COLUMN col DROP DEFAULT
设置/取消 NOT NULLALTER COLUMN col SET NOT NULL
ALTER COLUMN col DROP NOT NULL
修改数据类型ALTER COLUMN col TYPE new_type [USING expr]
设置统计目标ALTER COLUMN col SET STATISTICS n
设置存储方式ALTER COLUMN col SET STORAGE {PLAIN|EXTERNAL|EXTENDED|MAIN}

示例：

-- 修改类型（自动转换）
ALTER TABLE users ALTER COLUMN email TYPE VARCHAR(255);

-- 复杂转换（使用 USING）
ALTER TABLE orders ALTER COLUMN total TYPE NUMERIC(10,2)
    USING total::NUMERIC(10,2);

-- 设置默认值
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- 取消默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- 设置 NOT NULL（需确保无 NULL 值）
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- 取消 NOT NULL
ALTER TABLE users ALTER COLUMN email DROP NOT NULL;

4. 重命名列 RENAME COLUMN

ALTER TABLE users RENAME COLUMN old_name TO new_name;

-- 示例
ALTER TABLE users RENAME COLUMN fullname TO full_name;

5. 添加约束 ADD CONSTRAINT

-- 主键
ALTER TABLE users ADD CONSTRAINT pk_users PRIMARY KEY (id);

-- 唯一键
ALTER TABLE users ADD CONSTRAINT uq_users_email UNIQUE (email);

-- 外键
ALTER TABLE orders ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE ON UPDATE RESTRICT;

-- CHECK 约束
ALTER TABLE products ADD CONSTRAINT chk_price_positive
    CHECK (price > 0);

-- 排除约束（Exclusion Constraint，GiST）
ALTER TABLE reservations ADD CONSTRAINT excl_room_time
    EXCLUDE USING GIST (room_id WITH =, tsrange(start_time, end_time) WITH &&);

6. 删除约束 DROP CONSTRAINT

ALTER TABLE users DROP CONSTRAINT uq_users_email;

-- 级联删除
ALTER TABLE users DROP CONSTRAINT fk_orders_user CASCADE;

7. 重命名表 RENAME TO

ALTER TABLE old_table RENAME TO new_table;

8. 移动到新 Schema

ALTER TABLE public.users SET SCHEMA private;

9. 更改所有者

ALTER TABLE users OWNER TO new_owner_role;

10. 更改表空间

ALTER TABLE big_table SET TABLESPACE fast_ssd;

11. 设置存储参数（如 fillfactor）

-- 适合频繁更新的表
ALTER TABLE users SET (fillfactor = 80);

-- 重置
ALTER TABLE users RESET (fillfactor);

12. 分区表操作

-- 附加分区
ALTER TABLE orders_all
    ATTACH PARTITION orders_2025 FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- 分离分区（转为普通表）
ALTER TABLE orders_all DETACH PARTITION orders_2020;

13. 行级安全（RLS）

-- 启用
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- 创建策略
CREATE POLICY employee_policy ON documents
    FOR ALL TO employee_role
    USING (department = current_user_department());

-- 禁用
ALTER TABLE documents DISABLE ROW LEVEL SECURITY;

14. 继承控制

-- 子表继承父表
ALTER TABLE regional_sales INHERIT sales;

-- 取消继承
ALTER TABLE regional_sales NO INHERIT sales;

四、批量操作示例（一次执行多动作）

ALTER TABLE employees
    ADD COLUMN salary DECIMAL(10,2) DEFAULT 0,
    ADD COLUMN department_id INT,
    ADD CONSTRAINT fk_dept FOREIGN KEY (department_id) REFERENCES departments(id),
    ALTER COLUMN email SET NOT NULL,
    RENAME COLUMN dept TO department,
    DROP COLUMN IF EXISTS temp_flag;

五、常见错误与避坑

错误原因解决
cannot drop column ... because other objects depend on it有外键、视图、触发器依赖用 CASCADE 或先删除依赖
column "email" contains null values设置 NOT NULL 时有 NULL先 UPDATE 填充默认值
type change would lose data类型转换不安全（如 TEXT → INT）使用 USING 显式转换
constraint exists重复添加唯一约束检查 pg_constraint
cannot alter type of column used by index索引依赖该列类型先 DROP INDEX，再修改

六、系统表查询辅助

-- 查看表所有列
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'users';

-- 查看约束
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'users'::regclass;

-- 查看依赖
SELECT * FROM pg_depend WHERE refobjid = 'users'::regclass;

七、推荐脚本：安全添加 NOT NULL 列

-- 1. 添加可空列 + 默认值
ALTER TABLE users ADD COLUMN verified BOOLEAN DEFAULT FALSE;

-- 2. 回填历史数据（可选）
UPDATE users SET verified = TRUE WHERE email LIKE '%@example.com';

-- 3. 设置 NOT NULL
ALTER TABLE users ALTER COLUMN verified SET NOT NULL;

-- 4. 移除默认值（可选）
ALTER TABLE users ALTER COLUMN verified DROP DEFAULT;

八、性能注意事项

操作是否锁表建议
ADD COLUMN + DEFAULT轻量锁（ACCESS EXCLUSIVE 短暂）生产可接受
ADD COLUMN 无默认元数据操作，几乎不锁推荐
ALTER COLUMN TYPE重写表（全表锁）大表慎用，建议新建表
DROP COLUMN重写表大表用 ALTER TABLE ... DROP COLUMN ... CASCADE 需评估
ADD CONSTRAINT验证全表大表建议 NOT VALID + VALIDATE 分步

分步添加约束（避免长锁）

-- 1. 添加但不立即校验
ALTER TABLE orders ADD CONSTRAINT chk_positive CHECK (amount > 0) NOT VALID;

-- 2. 后台校验
ALTER TABLE orders VALIDATE CONSTRAINT chk_positive;

九、完整示例：用户表升级

-- 原始表
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email TEXT,
    name TEXT
);

-- 升级脚本
ALTER TABLE users
    ADD COLUMN created_at TIMESTAMP DEFAULT NOW(),
    ADD COLUMN updated_at TIMESTAMP,
    ADD COLUMN status VARCHAR(20) DEFAULT 'active',
    ADD COLUMN profile JSONB,
    ALTER COLUMN email SET NOT NULL,
    RENAME COLUMN name TO full_name,
    ADD CONSTRAINT uq_users_email UNIQUE (email),
    ADD CONSTRAINT chk_status CHECK (status IN ('active', 'inactive', 'banned'));

-- 添加更新时间触发器
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at();

十、速查表

命令示例
添加列ADD COLUMN age INT
删除列DROP COLUMN temp
修改类型ALTER COLUMN col TYPE BIGINT USING col::BIGINT
设置默认ALTER COLUMN col SET DEFAULT 0
设置 NOT NULLALTER COLUMN col SET NOT NULL
添加主键ADD PRIMARY KEY (id)
添加唯一ADD UNIQUE (email)
添加外键ADD FOREIGN KEY (uid) REFERENCES users(id)
重命名表RENAME TO new_name
移动 schemaSET SCHEMA archive

需要我生成一个 ALTER TABLE 的自动化迁移脚本模板（支持 Python/Shell）吗？

类似文章

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注