PostgreSQL中ON CONFLICT场景下新增带时间戳条目问题

你的核心问题在于**name字段的UNIQUE约束**——这个约束会阻止同一名称的多条记录插入，导致ON CONFLICT只能触发更新现有行的逻辑，无法新增独立条目。要实现名称相同但邮箱变更时新增行的需求，需要从表结构和插入逻辑两方面调整：

1. 修改表结构，移除name的唯一约束 #

如果需要保存同一客户的历史邮箱变更记录，必须去掉name字段的UNIQUE限制，让数据库允许同名的多条记录存在：

CREATE TABLE customers (
    user_id serial PRIMARY KEY,
    name VARCHAR, -- 移除UNIQUE约束
    email VARCHAR NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL -- 建议用TIMESTAMPTZ类型替代VARCHAR，方便时间操作
);

可选：添加复合唯一约束防止重复提交 #

如果要避免完全相同的记录（如同一名称、邮箱、时间戳重复插入），可以添加复合唯一约束：

CREATE TABLE customers (
    user_id serial PRIMARY KEY,
    name VARCHAR,
    email VARCHAR NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL,
    UNIQUE(name, email, timestamp) -- 阻止完全重复的记录插入
);

2. 调整插入逻辑，直接新增记录 #

移除name的唯一约束后，无需使用ON CONFLICT，直接插入即可新增独立行：

-- 初始数据插入
INSERT INTO 
    customers (name, email, timestamp)
VALUES 
    ('IBM', 'contact@ibm.com', '2016-06-22 19:10:25-07'),
    ('Microsoft', 'contact@microsoft.com', '2016-06-29 11:10:24-07'),
    ('Intel', 'contact@intel.com', '2017-08-22 05:10:26-07')
RETURNING *;

-- 插入Microsoft的新邮箱记录，直接生成新行
INSERT INTO customers (name, email, timestamp)
VALUES('Microsoft','hotline@microsoft.com', '2019-05-02 19:10:02-07') -- 修正原时间戳格式错误
RETURNING *;

3. 如何查询每个客户的最新记录 #

如果需要快速获取每个客户的最新邮箱信息，可以使用窗口函数：

SELECT user_id, name, email, timestamp
FROM (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY name ORDER BY timestamp DESC) AS rn
    FROM customers
) t
WHERE rn = 1;

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