PostgreSQL / 系统课时
修改表与列
修改表与列
本课解决已建表结构需要继续演进的问题。你将使用 ALTER TABLE 添加、重命名、修改和删除列,并学会在执行前检查数据兼容性与依赖关系。
学习目标
- 使用
ALTER TABLE完成常见列结构变更 - 判断类型、非空约束和默认值修改的前置条件
- 通过系统视图验证修改结果
一条语句承担一种变更
PostgreSQL 使用 ALTER TABLE 表名 操作 修改现有表。生产环境中建议把高风险变更拆开执行,便于定位失败步骤,也能减少一次操作影响过多对象。常用形式如下:
ADD COLUMN:新增列RENAME COLUMN:重命名列ALTER COLUMN:修改类型、默认值或非空约束DROP COLUMN:删除列
完成一次表结构演进
下面脚本创建演示表,再依次新增库存列、重命名标题列、扩大价格精度并删除说明列。脚本可在空白测试数据库中直接运行。
sql
DROP TABLE IF EXISTS products;
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title varchar(100) NOT NULL,
description text,
price numeric(10, 2)
);
INSERT INTO products (title, description, price)
VALUES ('机械键盘', '87 键有线键盘', 299.00);
-- 新列同时设置默认值和非空约束,现有记录会得到 0
ALTER TABLE products
ADD COLUMN stock integer NOT NULL DEFAULT 0;
ALTER TABLE products
RENAME COLUMN title TO name;
ALTER TABLE products
ALTER COLUMN price TYPE numeric(12, 2);
ALTER TABLE products
ALTER COLUMN price SET DEFAULT 0.00;
-- 删除列会永久移除该列中的数据
ALTER TABLE products
DROP COLUMN description;
SELECT column_name, data_type, is_nullable
FROM information_schema.columns
WHERE table_schema = 'public'
AND table_name = 'products'
ORDER BY ordinal_position;预期结果
text
column_name | data_type | is_nullable
-------------+-------------------+------------
id | bigint | NO
name | character varying | NO
price | numeric | YES
stock | integer | NO
(4 rows)information_schema.columns 展示的是修改后的真实结构。客户端对空格和边框的显示可能略有不同,但列名、类型和可空性应一致。
修改类型与非空约束的边界
当旧类型不能自动转换为新类型时,需要使用 USING 指定转换表达式。例如文本列只保存数字时,可写成 ALTER COLUMN age TYPE integer USING age::integer。只要存在无法转换的值,整条语句就会失败,不会只修改部分记录。
设置 NOT NULL 前,必须先清理该列中的空值。默认值只影响后续插入,不能自动修复已有的 NULL;应先执行数据更新,再添加非空约束。
删除列前检查依赖
视图、索引、约束或其他对象可能依赖待删除列。默认情况下,PostgreSQL 会阻止破坏依赖的操作。DROP COLUMN ... CASCADE 会连带删除依赖对象,生产环境应先查询依赖并确认影响,而不是直接使用 CASCADE。
较大的表结构变更可能持有较强锁并阻塞读写。上线前应在同规模测试环境测量耗时,并安排维护窗口或采用分阶段迁移。
按自己的节奏,确认真正理解。