如何自动填充PostgreSQL中新增的外键引用列？

完全可以用纯PostgreSQL语句实现，无需手动填充或编写脚本，具体步骤如下：

1. 新增type_id列（暂不添加外键约束） #

先添加允许为空的列，避免因数据未填充触发约束错误：

ALTER TABLE properties ADD COLUMN type_id INT;

2. 批量自动填充type_id数据 #

通过关联types表，用properties的type字段匹配types表的对应字段（假设types表有与properties.type匹配的字段，比如type_name，请根据实际表结构调整该字段名），执行UPDATE语句完成批量填充：

UPDATE properties p
SET type_id = t.type_id
FROM types t
WHERE p.type = t.type_name; -- 替换为你实际的匹配字段

3. 添加外键约束 #

数据填充完成后，为type_id添加外键约束，若业务要求该字段非空，可同时设置NOT NULL：

ALTER TABLE properties 
ALTER COLUMN type_id SET NOT NULL,
ADD CONSTRAINT fk_properties_type FOREIGN KEY (type_id) REFERENCES types(type_id);

可选：后续自动维护（按需使用） #

如果希望后续properties的type字段更新时，type_id能自动同步，可以创建触发器函数和触发器：

-- 创建触发器函数
CREATE OR REPLACE FUNCTION sync_type_id()
RETURNS TRIGGER AS $$
BEGIN
  SELECT type_id INTO NEW.type_id FROM types WHERE type_name = NEW.type;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trigger_sync_type_id
BEFORE INSERT OR UPDATE OF type ON properties
FOR EACH ROW EXECUTE FUNCTION sync_type_id();

若业务不再需要原type字段，填充完成后可删除：

ALTER TABLE properties DROP COLUMN type;

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