垦荒学园

简体中文

English

简体中文

English

切换主题

PostgreSQL 修改表 ​

概述 ​

ALTER TABLE 用于修改现有表的结构，包括添加、删除或修改列，添加或删除约束等。

添加列 ​

sql
-- 添加单个列
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- 添加带默认值的列
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';

-- 添加带约束的列
ALTER TABLE users ADD COLUMN email VARCHAR(100) NOT NULL;

-- 添加多个列
ALTER TABLE users 
    ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at TIMESTAMP;

删除列 ​

sql
-- 删除单个列
ALTER TABLE users DROP COLUMN phone;

-- 删除多个列
ALTER TABLE users 
    DROP COLUMN phone,
    DROP COLUMN fax;

-- 级联删除（删除依赖对象）
ALTER TABLE users DROP COLUMN email CASCADE;

-- 如果存在则删除
ALTER TABLE users DROP COLUMN IF EXISTS phone;

修改列 ​

重命名列 ​

sql
-- 重名列列
ALTER TABLE users RENAME COLUMN name TO full_name;

-- 重命名多个列
ALTER TABLE users RENAME COLUMN addr TO address;
ALTER TABLE users RENAME COLUMN tel TO phone;

更改数据类型 ​

sql
-- 更改列类型
ALTER TABLE users ALTER COLUMN age TYPE INTEGER;

-- 使用USING转换数据
ALTER TABLE users 
    ALTER COLUMN price TYPE NUMERIC(10,2) 
    USING price::NUMERIC(10,2);

-- 更改字符串长度
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);

设置/删除默认值 ​

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

-- 删除默认值
ALTER TABLE users ALTER COLUMN status DROP DEFAULT;

-- 设置当前时间戳为默认值
ALTER TABLE orders ALTER COLUMN created_at SET DEFAULT CURRENT_TIMESTAMP;

设置/删除NOT NULL ​

sql
-- 设置NOT NULL约束
ALTER TABLE users ALTER COLUMN email SET NOT NULL;

-- 删除NOT NULL约束
ALTER TABLE users ALTER COLUMN phone DROP NOT NULL;

添加约束 ​

主键约束 ​

sql
-- 添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 添加命名主键
ALTER TABLE users ADD CONSTRAINT users_pkey PRIMARY KEY (id);

-- 添加复合主键
ALTER TABLE order_items 
    ADD PRIMARY KEY (order_id, product_id);

外键约束 ​

sql
-- 添加外键
ALTER TABLE orders 
    ADD FOREIGN KEY (user_id) REFERENCES users(id);

-- 添加命名外键
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) REFERENCES users(id);

-- 添加带级联的外键
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE;

唯一约束 ​

sql
-- 添加唯一约束
ALTER TABLE users ADD UNIQUE (email);

-- 添加命名唯一约束
ALTER TABLE users 
    ADD CONSTRAINT users_email_unique UNIQUE (email);

-- 添加复合唯一约束
ALTER TABLE products 
    ADD CONSTRAINT products_name_category_unique 
    UNIQUE (name, category_id);

检查约束 ​

sql
-- 添加检查约束
ALTER TABLE products 
    ADD CHECK (price > 0);

-- 添加命名检查约束
ALTER TABLE products 
    ADD CONSTRAINT products_price_check 
    CHECK (price > 0 AND price < 1000000);

-- 添加复杂检查约束
ALTER TABLE users 
    ADD CONSTRAINT users_age_check 
    CHECK (age >= 18 AND age <= 120);

删除约束 ​

sql
-- 删除主键
ALTER TABLE users DROP CONSTRAINT users_pkey;

-- 删除外键
ALTER TABLE orders DROP CONSTRAINT fk_orders_users;

-- 删除唯一约束
ALTER TABLE users DROP CONSTRAINT users_email_unique;

-- 删除检查约束
ALTER TABLE products DROP CONSTRAINT products_price_check;

-- 级联删除约束
ALTER TABLE users DROP CONSTRAINT users_pkey CASCADE;

重命名表 ​

sql
-- 重命名表
ALTER TABLE users RENAME TO customers;

-- 重命名表（如果存在）
ALTER TABLE IF EXISTS old_table RENAME TO new_table;

更改表所有者 ​

sql
-- 更改表所有者
ALTER TABLE users OWNER TO new_owner;

-- 更改多个表的所有者
ALTER TABLE users OWNER TO admin;
ALTER TABLE orders OWNER TO admin;

更改表空间 ​

sql
-- 移动表到不同的表空间
ALTER TABLE users SET TABLESPACE fast_storage;

-- 移动表及其索引
ALTER TABLE users SET TABLESPACE fast_storage;

实际示例 ​

示例1：用户表重构 ​

sql
-- 添加新列
ALTER TABLE users ADD COLUMN last_login TIMESTAMP;
ALTER TABLE users ADD COLUMN login_count INTEGER DEFAULT 0;

-- 修改现有列
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);

-- 添加检查约束
ALTER TABLE users ADD CONSTRAINT users_age_check CHECK (age >= 18);

-- 重命名列
ALTER TABLE users RENAME COLUMN addr TO address;

示例2：订单表优化 ​

sql
-- 添加外键约束
ALTER TABLE orders 
    ADD CONSTRAINT fk_orders_users 
    FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE;

-- 添加状态检查
ALTER TABLE orders 
    ADD CONSTRAINT orders_status_check 
    CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));

-- 添加金额检查
ALTER TABLE orders 
    ADD CONSTRAINT orders_amount_check 
    CHECK (total_amount >= 0);

-- 添加时间戳
ALTER TABLE orders 
    ADD COLUMN created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    ADD COLUMN updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP;

示例3：产品表扩展 ​

sql
-- 添加新字段
ALTER TABLE products 
    ADD COLUMN description TEXT,
    ADD COLUMN image_url VARCHAR(500),
    ADD COLUMN is_active BOOLEAN DEFAULT true;

-- 修改价格列
ALTER TABLE products 
    ALTER COLUMN price TYPE NUMERIC(10,2),
    ALTER COLUMN price SET NOT NULL;

-- 添加约束
ALTER TABLE products 
    ADD CONSTRAINT products_price_check CHECK (price > 0),
    ADD CONSTRAINT products_stock_check CHECK (stock >= 0);

-- 添加唯一约束
ALTER TABLE products 
    ADD CONSTRAINT products_sku_unique UNIQUE (sku);

最佳实践 ​

  1. 备份数据：在修改表结构前始终备份数据
  2. 使用事务：在事务中执行 ALTER TABLE 操作
  3. 命名约束：为约束指定有意义的名称
  4. 测试环境：先在测试环境中测试修改
  5. 避免锁表：注意某些操作会锁定整个表
  6. 使用 IF EXISTS：使用 IF EXISTS 避免错误

注意事项 ​

  • ALTER TABLE 操作可能会锁定表
  • 某些修改需要重写整个表
  • 添加 NOT NULL 列时需要提供默认值或确保无 NULL 值
  • 外键约束会影响性能
  • 大表的结构修改可能需要很长时间

小结 ​

ALTER TABLE 是 PostgreSQL 中修改表结构的强大工具：

  • 添加/删除列：扩展或精简表结构
  • 修改列属性：更改数据类型、默认值、NULL 约束
  • 管理约束：添加或删除主键、外键、唯一和检查约束
  • 重命名：重命名表和列
  • 其他操作：更改所有者、表空间等

合理使用 ALTER TABLE 可以灵活地调整数据库结构以适应业务需求。