sql - Postgres upsert 使用冲突 - 冲突条件下的多个字段

问题描述

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

这是我的架构：

id | postID | userID | type 
 1      2        1      like

现在，如果用户已经喜欢该项目，现在他们决定不喜欢它 - 我想更新该type字段，从like到dislike。

同样，如果他们不喜欢某些东西，现在决定喜欢它，我想执行相反的更新。

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

这意味着我需要在中实现一个 upsert 语句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

标签： sqlpostgresqlsql-updatesql-insertupsert

如果要限制对一行的更新次数，可以使用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，其中 type <> '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

sql - Postgres upsert 使用冲突 - 冲突条件下的多个字段

问题描述

解决方案

推荐阅读