我将提供一个简化版的问题实例。
我有两个表:reviews
和users
。
reviews
被更新了一堆用户发表的评论。获取评论的过程也会返回提交它的用户的信息(某些用户数据会经常更改)。
每当我批量更新reviews
时,我想使用COPY
来批量更新users
。对于users
而言,问题出现在获取的数据中包含来自同一用户的两个或多个评论的情况下。如果我只是简单地使用INSERT ON CONFLICT
,由于一个INSERT
语句无法两次更新同一行,我可能会遇到错误。
使用SELECT DISTINCT
可以解决这个问题,但我也想保证将最新的数据插入到users
表中。这就是我做的方式。请记住,我是批量执行以下操作:
1. 创建一个临时表,以便我们可以对其进行COPY
输入输出。
CREATE TEMPORARY TABLE users_temp (
id uuid,
stat_1 integer,
stat_2 integer,
account_age_in_mins integer);
2. 将数据 COPY
到临时表中
COPY users_temp (
id,
stat_1,
stat_2,
account_age_in_mins) FROM STDIN CSV ENCODING 'utf-8';
3. 锁定users
表并执行INSERT ON CONFLICT
LOCK TABLE users in EXCLUSIVE MODE;
INSERT INTO users SELECT DISTINCT ON (1)
users_temp.id,
users_temp.stat_1,
users_temp.stat_2,
users_temp.account_age_in_mins
FROM users_temp
ORDER BY 1, 4 DESC, 2, 3
ON CONFLICT (id) DO UPDATE
SET
stat_1 = EXCLUDED.stat_1,
stat_2 = EXCLUDED.stat_2,
account_age_in_mins = EXCLUDED.account_age_in_mins';
我在第3)步中进行SELECT DISTINCT
和ORDER BY
的原因是因为:
- 只想返回一次重复的行。
- 从这些重复的行中,通过按
account_age_in_mins
排序来确保获取最新的记录。
这是实现我的目标的正确方法吗?