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。

较大的表结构变更可能持有较强锁并阻塞读写。上线前应在同规模测试环境测量耗时,并安排维护窗口或采用分阶段迁移。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

修改表与列

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通