如何在PostgreSQL 9.5中执行INSERT INTO SELECT和ON DUPLICATE UPDATE操作？

Question

我正试图在PostgreSQL中完成以下操作。

INSERT INTO blog_sums ( blog_id, date, total_comments)
    SELECT blog_id, '2016-09-22', count(comment_id) as total_comments_update
    FROM blog_comments
    WHERE date = '2016-09-22'
    GROUP BY blog_id         
ON CONFLICT (blog_id ,date)
DO UPDATE SET blog_sums.total_comments = total_comments_update;

我在日期+博客ID上有一个唯一的键，但我不断收到错误消息：

错误：列“total_comments_update”不存在

我正在寻找在此情况下更新重复/冲突的“正确”和最有效的方法

我的表格如下：

blog_comments (blog_id, comment_id, comment, date)
blog_sums ( blog_id, date, total_comments) . unique on blog_id+date

感谢

- Yuval Kaufman

Answer 1

在 DO UPDATE SET 子句中，您无法从选择中访问列别名。 您可以使用 excluded 表别名，该别名包括由于冲突而未能插入的所有行：

INSERT INTO blog_sums ( blog_id, date, total_comments)
    SELECT blog_id, '2016-09-22', count(comment_id) as total_comments_update
    FROM blog_comments
    WHERE date = '2016-09-22'
    GROUP BY blog_id         
ON CONFLICT (blog_id ,date)
DO UPDATE SET total_comments = excluded.total_comments;

因此，最后一行中的excluded.total_comments是指我们试图插入但由于冲突而无法插入的total_comments值。

- redneb

---

不要忘记使用GROUP BY，否则你会在postgresql 9.6.1中得到以下错误：ERROR: ON CONFLICT DO UPDATE命令不能再次影响同一行HINT: 确保在同一命令中没有提议插入具有重复约束值的行。 - user1767316