如何在PostgreSQL的JSONB列插入前为JSON对象添加created_date和updated_date

没问题！完全可以在插入data列前给目标JSON对象追加created_date字段，甚至还能实现自动维护created_date和updated_date的需求，下面给你两种实用的方案：

方案一：插入时直接构造带日期的JSONB #

如果只是单次插入需要添加日期字段，直接在INSERT语句里用PostgreSQL的JSONB函数拼接字段就行，操作简单直接：

方式1：用JSONB拼接操作符|| #

INSERT INTO your_table_name (request_id, data)
VALUES (
    12, -- 这里的request_id与JSON内的request字段对应，可根据实际调整
    '{"request": 12, "createdby": "sam"}'::jsonb 
    || jsonb_build_object(
        'created_date', CURRENT_TIMESTAMP,
        'updated_date', CURRENT_TIMESTAMP
    )
);

方式2：用jsonb_set逐个添加字段（适合字段较多的场景） #

INSERT INTO your_table_name (request_id, data)
VALUES (
    12,
    jsonb_set(
        jsonb_set(
            '{"request": 12, "createdby": "sam"}'::jsonb,
            '{created_date}',
            to_jsonb(CURRENT_TIMESTAMP)
        ),
        '{updated_date}',
        to_jsonb(CURRENT_TIMESTAMP)
    )
);

方案二：用触发器自动维护日期字段 #

如果希望每次插入或更新数据时，都自动同步created_date和updated_date（比如更新时自动刷新updated_date），用触发器会更省心，一劳永逸：

步骤1：创建触发器函数 #

CREATE OR REPLACE FUNCTION maintain_json_dates()
RETURNS TRIGGER AS $$
BEGIN
    -- 插入操作：同时设置created_date和updated_date
    IF TG_OP = 'INSERT' THEN
        NEW.data = NEW.data || jsonb_build_object(
            'created_date', CURRENT_TIMESTAMP,
            'updated_date', CURRENT_TIMESTAMP
        );
    -- 更新操作：仅更新updated_date
    ELSIF TG_OP = 'UPDATE' THEN
        NEW.data = NEW.data || jsonb_build_object(
            'updated_date', CURRENT_TIMESTAMP
        );
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

步骤2：给目标表绑定触发器 #

CREATE TRIGGER trigger_json_date_maintenance
BEFORE INSERT OR UPDATE ON your_table_name
FOR EACH ROW EXECUTE FUNCTION maintain_json_dates();

之后的使用方式 #

绑定触发器后，你只需要正常插入原始JSON即可，触发器会自动帮你添加日期字段：

INSERT INTO your_table_name (request_id, data)
VALUES (12, '{"request": 12, "createdby": "sam"}'::jsonb);

当你更新数据时，updated_date会自动刷新为当前时间：

UPDATE your_table_name 
SET data = data || '{"status": "processed"}'::jsonb 
WHERE request_id = 12;

小提示 #

• CURRENT_TIMESTAMP生成的是带时区的标准ISO 8601格式日期，如果你需要不带时区的日期，可以改用LOCALTIMESTAMP或CURRENT_DATE；
• JSONB中的日期以字符串形式存储，这种标准格式可以直接被PostgreSQL的日期函数识别，后续查询时无需额外转换。

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