Node服务向PostgreSQL公共表插入键值对报ON CONFLICT约束错误

PostgreSQL的INSERT ... ON CONFLICT（即upsert）语法有强制前置要求：你在ON CONFLICT后指定的冲突判断列，必须对应已存在的**唯一约束（UNIQUE）**或排他约束，数据库不会自动按你写的列名判断冲突。
你两次调整SQL语句都只修改了查询语法，没有修改表结构，缺少对应唯一约束，所以始终触发42P10错误。
你第二次修改时加的WHERE ((key)::text = $1::text)是无效写法：ON CONFLICT后的WHERE子句仅用于匹配已存在的部分唯一索引，不能用来自定义冲突判断规则，没有对应约束的前提下写再多判断逻辑也无法生效。

1. 给key字段添加唯一约束 #

先执行如下SQL给public schema下stats表的key列加唯一约束，这是upsert语法生效的必要前提：

ALTER TABLE public.stats ADD CONSTRAINT stats_key_unique UNIQUE (key);

执行前注意：如果表中已经存在重复的key值，这条SQL会执行失败。需要先清理重复数据，保证每个key只对应一条记录后，再执行添加约束的操作。

2. 修正Node服务端代码 #

约束添加完成后，不需要写多余的冲突判断条件，使用标准upsert写法即可正常运行，优化后的代码如下：

let value = await db.query({
    text: "INSERT INTO public.stats (key, value) VALUES ($1, $2) ON CONFLICT (key) DO UPDATE SET value = EXCLUDED.value;",
    values: [wantedStamp, inDb] 
});
const rep: Reply_ip_get_background_image_timestamp = {
    name: wantedStamp,
    date: inDb
};
res.status(200).json(rep);

代码里做了两处必要调整：

• 用EXCLUDED.value替代直接传$2，这是PostgreSQL upsert的标准写法，代表本次插入操作尝试写入的value值，可避免后续字段调整时出现参数匹配错误
• 调整了响应方法的顺序：先调用status(200)设置状态码，再调用json()返回响应体，符合Express等Node服务端框架的链式调用规范，避免部分场景下状态码返回异常。

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