PostgreSQL中创建INSERT冲突时Upsert规则的技术问询

嘿，你想要的是插入数据时遇到主键冲突就自动更新对应字段的Upsert功能，不过不用通过CREATE RULE来实现——PostgreSQL从9.5版本开始就提供了更简洁、可靠的原生语法：INSERT ... ON CONFLICT ... DO UPDATE，这也是当前实现Upsert的最佳实践。

先说说你伪代码里的问题 #

CREATE RULE的语法本身就不正确，而且Rule机制是通过重写SQL查询来实现逻辑，在Upsert场景下很容易出现并发异常、逻辑冲突等问题，并不适合这种需求。

正确的Upsert写法 #

直接在INSERT语句中添加ON CONFLICT子句即可，完整SQL如下：

INSERT INTO public.rssmodel (link, person, places, date)
VALUES ('要插入的链接', '对应人物信息', '对应地点信息', '对应日期')
ON CONFLICT (link) DO UPDATE
SET
  person = EXCLUDED.person,
  places = EXCLUDED.places,
  date = EXCLUDED.date;

关键部分解释 #

• ON CONFLICT (link)：指定触发冲突的列（这里link是主键，所以当插入的link已存在时触发冲突）
• EXCLUDED：PostgreSQL的特殊关键字，代表原本要插入的那条记录的行数据，用它可以直接把新插入的值同步到现有行的对应字段
• 如果是后端程序用参数化查询（比如Python的psycopg2、Java的JDBC），可以用占位符替代具体值，示例如下：

  INSERT INTO public.rssmodel (link, person, places, date)
  VALUES ($1, $2, $3, $4)
  ON CONFLICT (link) DO UPDATE
  SET
    person = EXCLUDED.person,
    places = EXCLUDED.places,
    date = EXCLUDED.date;
  

进阶：带条件的更新 #

如果你需要更灵活的逻辑（比如只有新日期比旧日期晚才更新），可以在DO UPDATE后添加WHERE子句：

INSERT INTO public.rssmodel (link, person, places, date)
VALUES ('要插入的链接', '对应人物信息', '对应地点信息', '2024-05-20')
ON CONFLICT (link) DO UPDATE
SET
  person = EXCLUDED.person,
  places = EXCLUDED.places,
  date = EXCLUDED.date
WHERE EXCLUDED.date > rssmodel.date; -- 仅当新日期更晚时才执行更新

这种原生Upsert是原子操作，能有效避免并发场景下的冲突和数据不一致问题，比用Rule要可靠得多。

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