postgres upsert使用on conflict-on conflict条件中的多个字段

ctrmrzij  于 2021-08-13  发布在  Java
关注(0)|答案(1)|浏览(967)

我正在尝试实现一个概念,即喜欢/不喜欢一个项目 postgres db-当用户喜欢/不喜欢某个东西时,我想在db中插入一个项来表示它。
这是我的模式:

id | postID | userID | type 
 1      2        1      like

现在,如果用户已经喜欢这个项目,现在他们决定不喜欢它-我想更新 type 字段,从 likedislike .
同样,如果他们不喜欢某个东西,现在决定喜欢它,我想执行相反的更新。
此外,用户只能喜欢/不喜欢某个东西一次-因此,如果用户以前喜欢/不喜欢这个帖子,现在又决定喜欢/不喜欢它,什么都不应该发生。
这意味着我需要在 postgres ,如果用户以前没有与文章交互,则插入新行,并更新 type 字段,如果指定了 postID + userID + type 已经存在。
我想用 on conflict 语法-

INSERT INTO table_name(postID,userID,type) 
VALUES(2,1,'like') 
ON CONFLICT (????) DO UPDATE 
  SET type = 'like'

但我不知道该把什么传给 ON CONFLICT 节,因为匹配需要在多个字段上进行。
我考虑在(postid,userid)字段上设置一个唯一的索引-类似这样: create unique index idx_1 on table (postID, userID) 问题是我想在将来使用这个数据库来存储评论信息,并且允许一个用户对同一篇文章评论多次。
例如:

id | postID | userID | type 
 1      2       1      comment
 2      2       1      comment 
 3      2       1      like
woobm2wo

woobm2wo1#

如果要限制一行的更新次数,可以使用 check 约束和第二列计数更新。

alter table t add column num_updates int default 1 check (num_updates <= 2);

然后,如果要防止在类型上重复行,而不是 comment ,可以使用筛选的唯一索引:
在表名称(postid,userid)上创建唯一索引unq_table_name_postid_userid,其中键入<>'comment';
我想你可以用 on conflict :

INSERT INTO table_name (postID, userID, type) 
    VALUES(2, 1, 'like') 
    ON CONFLICT (postID, userID) DO UPDATE 
         SET type = 'like',
             num_updates = excluded.num_updates + 1;

这只允许一次更新。您可能需要更精确的逻辑,例如仅在 type 变化:

num_update = (excluded.num_updates = num_updates)::int + 1

相关问题