Postgres upsert using on conflict - on conflict condition中的多个字段

Question

我正在尝试在postgres数据库中实现喜欢/不喜欢某项的概念--当用户喜欢/不喜欢某件东西时，我想在我的数据库中插入一个项来表示这一点。

这是我的方案：

id | postID | userID | type 
 1      2        1      like 

现在，如果用户已经喜欢了该项目，但又决定不喜欢它--我想要将type字段从like更新为dislike。

类似地，如果他们不喜欢某个东西，现在决定喜欢它，我想执行相反的更新。

此外，用户只能喜欢/不喜欢某件事一次-因此，如果用户以前喜欢/不喜欢该帖子，现在决定再次喜欢/不喜欢它，则不应该发生任何事情。

这意味着我需要在postgres中实现一条upsert语句，如果用户以前没有与post交互过，则插入一个新行；如果具有指定postID + userID + type的行已经存在，则更新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 

Answer 1

如果要限制一行的更新次数，可以使用check约束和辅助列对更新进行计数。

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

然后，如果您希望防止comment以外的其他类型上出现重复行，则可以使用过滤后的唯一索引：

在table_name(postid，userid)上创建唯一索引注释，其中类型为<>‘unq_table_name_postid_userid’；

然后，我认为您可以使用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