PostgreSQL如何从users表同步数据至profiles表指定字段

一、一次性同步现有用户数据 #

如果要把users表中已有的数据批量同步到profiles表，直接用INSERT ... SELECT语句即可，同时避免重复插入已存在的user_id记录：

INSERT INTO profiles (user_id, profile_fullname)
SELECT id, user_fullname
FROM users
WHERE NOT EXISTS (
    SELECT 1 FROM profiles WHERE profiles.user_id = users.id
);

这条语句会遍历users表的每一行，将id映射为profiles的user_id，user_fullname映射为profile_fullname插入。NOT EXISTS子句确保不会重复插入已经在profiles表中有对应user_id的记录。

二、自动同步后续新增/更新的用户数据 #

如果希望后续users表新增用户或修改user_fullname时，profiles表能自动同步，需要借助PostgreSQL的触发器实现：

1. 创建触发器函数 #

先定义一个处理同步逻辑的函数：

CREATE OR REPLACE FUNCTION sync_user_to_profile()
RETURNS TRIGGER AS $$
BEGIN
    -- 新增用户时，自动在profiles插入对应记录
    IF TG_OP = 'INSERT' THEN
        INSERT INTO profiles (user_id, profile_fullname)
        VALUES (NEW.id, NEW.user_fullname)
        ON CONFLICT (user_id) DO NOTHING;
    -- 当user_fullname被修改时，同步更新profiles的对应字段
    ELSIF TG_OP = 'UPDATE' AND OLD.user_fullname != NEW.user_fullname THEN
        UPDATE profiles
        SET profile_fullname = NEW.user_fullname
        WHERE user_id = NEW.id;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

2. 绑定触发器到users表 #

创建两个触发器，分别监听users表的插入和更新操作：

-- 监听INSERT操作，新增用户时同步到profiles
CREATE TRIGGER trigger_sync_user_insert
AFTER INSERT ON users
FOR EACH ROW
EXECUTE FUNCTION sync_user_to_profile();

-- 监听UPDATE操作，user_fullname修改时同步更新profiles
CREATE TRIGGER trigger_sync_user_update
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION sync_user_to_profile();

这样配置后：

• 往users表插入新用户时，profiles表会自动生成对应user_id和profile_fullname的记录；
• 修改users表中用户的user_fullname时，profiles表中对应user_id的profile_fullname会自动更新。

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