PostgreSQL中如何用纯SQL为error_log表填充device_serial_number字段

原表结构 #

CREATE TABLE device (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    serial_number VARCHAR(20) UNIQUE
);

CREATE TABLE error_log (
    id SERIAL PRIMARY KEY,
    device_id INT NOT NULL REFERENCES device(id),
);   

需求 #

将device表的主键改为serial_number并删除自动生成的id字段；同时为error_log表新增device_serial_number字段并完成赋值，替代ORM逐行循环更新的方式，用纯SQL实现。

分步SQL实现 #

1. 新增error_log的关联字段 #

先给error_log添加允许为空的device_serial_number字段，后续再赋值：

ALTER TABLE error_log ADD COLUMN device_serial_number VARCHAR(20);

2. 批量赋值device_serial_number #

通过UPDATE ... FROM语法关联device表，一次性完成所有日志记录的字段赋值，替代ORM的逐行循环：

UPDATE error_log el
SET device_serial_number = d.serial_number
FROM device d
WHERE el.device_id = d.id;

3. 新增字段添加非空约束 #

赋值完成后，给device_serial_number添加非空约束（匹配原device_id的非空要求）：

ALTER TABLE error_log ALTER COLUMN device_serial_number SET NOT NULL;

4. 删除旧外键约束 #

由于要修改device表的主键，需先删除error_log中关联device.id的外键约束。先查询约束名：

SELECT conname FROM pg_constraint 
WHERE conrelid = 'error_log'::regclass 
AND confrelid = 'device'::regclass 
AND contype = 'f';

假设查询到的约束名为error_log_device_id_fkey，执行删除：

ALTER TABLE error_log DROP CONSTRAINT error_log_device_id_fkey;

5. 修改device表的主键 #

先删除原主键约束，再将serial_number设为主键：

-- 原serial生成的主键约束名通常为device_pkey，可通过上述pg_constraint查询确认
ALTER TABLE device DROP CONSTRAINT device_pkey;
ALTER TABLE device ADD PRIMARY KEY (serial_number);

6. 新增新的外键约束 #

给error_log.device_serial_number添加外键，关联device.serial_number：

ALTER TABLE error_log ADD CONSTRAINT error_log_device_serial_number_fkey 
FOREIGN KEY (device_serial_number) REFERENCES device(serial_number);

7. 删除冗余字段 #

最后删除device的旧主键id和error_log的旧关联字段device_id：

ALTER TABLE device DROP COLUMN id;
ALTER TABLE error_log DROP COLUMN device_id;

说明 #

• 上述步骤必须按顺序执行，因为存在依赖关系（比如必须先完成字段赋值，才能添加非空/外键约束；必须先删除旧外键，才能修改device的主键）。
• 纯SQL的批量更新相比ORM逐行循环，在数据量较大时效率提升显著，避免了应用层与数据库的多次交互开销。

内容的提问来源于stack exchange，提问作者user422005