PostgreSQL按create_date分区后Upsert重复行问题求助

针对PostgreSQL分区表与业务唯一键冲突导致Upsert失效的问题，推荐以下两种更优方案，兼顾分区优势与业务唯一性要求：

方案一：固定create_date并约束不可更新（推荐） #

核心思路是让create_date仅记录首次创建时间，不允许修改，这样业务唯一键zip_code+name与分区键create_date绑定，既满足PostgreSQL分区表的主键要求，又能正常使用ON CONFLICT Upsert：

1. 添加符合分区要求的主键

ALTER TABLE t_address ADD PRIMARY KEY (zip_code, name, create_date);

2. 创建触发器阻止修改create_date
   保证create_date一旦插入就固定，避免因字段变化导致重复行：

CREATE OR REPLACE FUNCTION prevent_create_date_update()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.create_date != NEW.create_date THEN
        RAISE EXCEPTION 'create_date字段不允许修改';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_prevent_create_date_update
BEFORE UPDATE ON t_address
FOR EACH ROW EXECUTE FUNCTION prevent_create_date_update();

3. 调整Upsert语句
   先查询是否存在对应zip_code+name的记录，复用已有create_date，不存在则用当前时间：

WITH existing_addr AS (
    SELECT create_date FROM t_address 
    WHERE zip_code = '目标邮编' AND name = '目标名称' 
    LIMIT 1
)
INSERT INTO t_address (zip_code, number, name, category, create_date, last_modified)
SELECT 
    '目标邮编', '更新后的门牌号', '目标名称', '更新后的分类',
    COALESCE(create_date, NOW()), NOW()
FROM existing_addr
ON CONFLICT (zip_code, name, create_date)
DO UPDATE SET 
    number = EXCLUDED.number, 
    category = EXCLUDED.category, 
    last_modified = EXCLUDED.last_modified;

该方案无需额外表，性能损耗低，完全保留分区的快速删除优势，适合大多数场景。

方案二：用辅助表+触发器维护业务唯一性 #

如果业务上必须允许修改create_date，可以通过辅助表存储全局唯一的zip_code+name对，用触发器同步并检查唯一性：

1. 创建辅助唯一表
   仅存储业务层面的唯一键，确保全局唯一：

CREATE TABLE t_address_unique (
    zip_code varchar(20) NOT NULL,
    name varchar(255) NOT NULL,
    PRIMARY KEY (zip_code, name)
);

2. 创建触发器函数检查唯一性
   插入/更新时先检查辅助表，阻止重复记录：

-- 插入前检查
CREATE OR REPLACE FUNCTION check_address_insert_unique()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO t_address_unique (zip_code, name)
    VALUES (NEW.zip_code, NEW.name) ON CONFLICT DO NOTHING;
    -- 检查是否插入成功（失败则说明已存在）
    IF NOT FOUND THEN
        RAISE EXCEPTION '已存在相同邮编和名称的地址记录';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_address_insert
BEFORE INSERT ON t_address
FOR EACH ROW EXECUTE FUNCTION check_address_insert_unique();

-- 更新时检查（防止修改zip_code/name导致重复）
CREATE OR REPLACE FUNCTION check_address_update_unique()
RETURNS TRIGGER AS $$
BEGIN
    IF OLD.zip_code != NEW.zip_code OR OLD.name != NEW.name THEN
        INSERT INTO t_address_unique (zip_code, name)
        VALUES (NEW.zip_code, NEW.name) ON CONFLICT DO NOTHING;
        IF NOT FOUND THEN
            RAISE EXCEPTION '已存在相同邮编和名称的地址记录';
        END IF;
        -- 删除旧的唯一键记录
        DELETE FROM t_address_unique 
        WHERE zip_code = OLD.zip_code AND name = OLD.name;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_address_update
BEFORE UPDATE ON t_address
FOR EACH ROW EXECUTE FUNCTION check_address_update_unique();

3. Upsert逻辑处理
   捕获触发器抛出的异常，执行更新操作：

DO $$
BEGIN
    INSERT INTO t_address (zip_code, number, name, category, create_date, last_modified)
    VALUES ('目标邮编', '门牌号', '目标名称', '分类', NOW(), NOW());
EXCEPTION
    WHEN unique_violation THEN
        UPDATE t_address 
        SET number = '门牌号', category = '分类', last_modified = NOW()
        WHERE zip_code = '目标邮编' AND name = '目标名称';
END $$;

该方案灵活性更高，但需要维护额外的辅助表，适合业务逻辑复杂的场景。

对原有思路的点评 #

• 移除分区：仅适合数据量较小的场景，大表定期清理会引发锁表、性能下降等问题，不推荐。
• 保留分区+自定义键约束：方案一就是该思路的优化，无需手动编写分区触发器，可借助pg_partman等工具实现自动分区管理。
• 插入前查询：高并发下存在竞态条件（查询后插入前，其他进程可能插入重复记录），且多次IO会降低性能，不推荐。

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