我来聊聊这种在PostgreSQL里用两张仅插入表存储实体全版本的实现思路，用User实体做例子会非常直观：

核心设计逻辑 #

我们把实体的属性拆成两类分开存储：

• 不可变属性：比如用户的注册初始邮箱、身份证号这类一旦设定就不会更改的信息，存在一张基础表中
• 可变属性：比如昵称、手机号、地址这类会频繁修改的信息，存在另一张版本表中

两张表都采用仅插入模式（通过触发器强制限制，绝不允许更新或删除旧数据），这样就能完整保留实体从创建到现在的每一个历史版本。

示例表结构 #

1. user表（存储主键与不可变属性） #

这张表只会在用户创建时插入一条记录，后续不会有任何修改操作：

CREATE TABLE "user" (
    id INT PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- 示例不可变属性：注册时的永久邮箱（假设不允许修改）
    permanent_email VARCHAR(255) NOT NULL
);

2. user_details表（存储可变属性的版本快照） #

每次用户修改可变属性时，我们都插入一条新记录，而不是更新旧数据，每一行都代表该用户的一个版本状态：

CREATE TABLE user_details (
    id SERIAL PRIMARY KEY,
    user_id INT NOT NULL REFERENCES "user"(id),
    updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    -- 示例可变属性
    nickname VARCHAR(50),
    phone VARCHAR(20),
    address TEXT,
    -- 可选：版本号字段，方便快速区分版本顺序
    version INT NOT NULL DEFAULT 1
);

强制仅插入模式的实现 #

为了确保两张表不会被意外更新或删除，我们可以通过触发器来拦截这类操作：

-- 定义触发器函数：阻止更新/删除操作
CREATE OR REPLACE FUNCTION prevent_modification()
RETURNS TRIGGER AS $$
BEGIN
    RAISE EXCEPTION '该表为仅插入模式，不允许执行更新或删除操作';
END;
$$ LANGUAGE plpgsql;

-- 给user表绑定触发器
CREATE TRIGGER user_no_modify
BEFORE UPDATE OR DELETE ON "user"
FOR EACH ROW EXECUTE FUNCTION prevent_modification();

-- 给user_details表绑定触发器
CREATE TRIGGER user_details_no_modify
BEFORE UPDATE OR DELETE ON user_details
FOR EACH ROW EXECUTE FUNCTION prevent_modification();

常用查询示例 #

获取用户的所有历史版本 #

SELECT 
    u.id AS user_id,
    u.permanent_email,
    ud.version,
    ud.nickname,
    ud.phone,
    ud.address,
    ud.updated_at AS version_created_at
FROM "user" u
JOIN user_details ud ON u.id = ud.user_id
WHERE u.id = 1
ORDER BY ud.updated_at DESC;

获取用户的最新版本 #

SELECT 
    u.id AS user_id,
    u.permanent_email,
    ud.nickname,
    ud.phone,
    ud.address,
    ud.updated_at AS latest_update_time
FROM "user" u
JOIN (
    SELECT * FROM user_details 
    WHERE user_id = 1 
    ORDER BY updated_at DESC 
    LIMIT 1
) ud ON u.id = ud.user_id;

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