如何在PostgreSQL中将所有'addr bytea'替换为'addr_id bigint'

直接执行ALTER TABLE 表名 ALTER COLUMN addr TYPE bigint会触发ERROR: cannot cast type bytea to bigint报错，原因是PostgreSQL默认没有提供bytea到bigint的类型转换规则，需按以下步骤通过映射表完成替换：

操作步骤 #

• 步骤1：创建地址映射表，存储bytea地址与bigint ID的唯一映射关系

CREATE TABLE addr_map (
    addr_id bigserial PRIMARY KEY,
    addr bytea UNIQUE NOT NULL
);

• 步骤2：将所有业务表中已有的bytea地址去重后插入映射表，自动生成对应ID
  如果有多个业务表包含addr列，需要依次执行插入，避免遗漏：

INSERT INTO addr_map (addr) 
SELECT DISTINCT addr FROM 业务表1 
ON CONFLICT (addr) DO NOTHING;

-- 有其他业务表的话重复执行上面的逻辑，替换表名即可

• 步骤3：为所有业务表新增addr_id列，类型为bigint

ALTER TABLE 业务表1 ADD COLUMN addr_id bigint;

• 步骤4：关联映射表为业务表的addr_id字段赋值

UPDATE 业务表1 t 
SET addr_id = m.addr_id 
FROM addr_map m 
WHERE t.addr = m.addr;

• 步骤5：（可选）确认所有addr_id都赋值完成后，可添加非空约束

ALTER TABLE 业务表1 ALTER COLUMN addr_id SET NOT NULL;

• 步骤6：删除原addr列，或重命名备份

-- 直接删除原列
ALTER TABLE 业务表1 DROP COLUMN addr;

-- 如需备份原数据可改为重命名
-- ALTER TABLE 业务表1 RENAME COLUMN addr TO addr_bytea_old;

• 步骤7：重建原依赖addr列的索引、约束，替换为addr_id字段

-- 示例：新建addr_id索引
CREATE INDEX idx_业务表1_addr_id ON 业务表1(addr_id);

后续适配注意事项 #

• 新数据写入时，需先查询addr_map表确认地址是否存在，不存在则先插入映射表获取新的addr_id，再写入业务表
• 操作前务必完成全量数据备份，大表操作建议先在测试环境验证耗时，避免生产环境锁表影响业务

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