如何在 PostgreSQL 9.5 中执行 INSERT INTO SELECT 和 ON DUPLICATE UPDATE？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/39663280/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to do INSERT INTO SELECT and ON DUPLICATE UPDATE in PostgreSQL 9.5?
提问by Yuval Kaufman
I'm trying to to do the following in PostgreSQL
我正在尝试在 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;
I have unique key on date + blog_id and I keep getting Error:
我在 date + blog_id 上有唯一键，但我不断收到错误：
ERROR: column "total_comments_update" does not exist
错误：“total_comments_update”列不存在
I'm looking for the "right" way and the most efficient way to do update on duplicate/conflict in this case
在这种情况下，我正在寻找“正确”的方式和最有效的方式来更新重复/冲突
my tables are
我的桌子是
blog_comments (blog_id, comment_id, comment, date)
blog_sums ( blog_id, date, total_comments) . unique on blog_id+date
Thanks
谢谢
回答by redneb
You cannot access the column aliases from the select in the DO UPDATE SETclause. You can use the excludedtable alias which includes all rows that failed to insert because of conflicts:
您无法从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;
So excluded.total_commentsin the last line refers to the value of total_commentsthat we tried to insert but we couldn't, because of the conflict.
所以excluded.total_comments在最后一行中指的total_comments是我们试图插入但由于冲突而无法插入的值。
