SQL 批量插入，如果在 Postgres 上发生冲突（批量更新插入），则更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/34514457/
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
Bulk insert, update if on conflict (bulk upsert) on Postgres
提问by MK Yung
I am writing a data-mining program, which bulk inserts user data.
我正在编写一个数据挖掘程序，它可以批量插入用户数据。
The current SQL is just a plain bulk insert:
当前的 SQL 只是一个普通的批量插入：
insert into USERS(
id, username, profile_picture)
select unnest(array['12345']),
unnest(array['Peter']),
unnest(array['someURL']),
on conflict (id) do nothing;
How do I do an update if on conflict? I tried:
如果发生冲突，我如何进行更新？我试过：
...
unnest(array['Peter']) as a,
unnest(array['someURL']) as b,
on conflict (id) do
update set
username = a,
profile_picture = b;
But it throws There is a column named "a" in table "*SELECT*", but it cannot be referenced from this part of the query.error.
但它抛出There is a column named "a" in table "*SELECT*", but it cannot be referenced from this part of the query.错误。
EDIT:
编辑：
Table of USERSis very simple:
表USERS很简单：
create table USERS (
id text not null primary key,
username text,
profile_picture text
);
回答by MK Yung
Turns out a special table named excludedcontains the row-to-be-inserted
(strange name though)
结果是一个名为的特殊表excluded包含要插入的行（虽然名字很奇怪）
insert into USERS(
id, username, profile_picture)
select unnest(array['12345']),
unnest(array['Peter']),
unnest(array['someURL'])
on conflict (id) do
update set
username = excluded.username,
profile_picture = excluded.profile_picture;
http://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
http://www.postgresql.org/docs/9.5/static/sql-insert.html#SQL-ON-CONFLICT
The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table...
ON CONFLICT DO UPDATE 中的 SET 和 WHERE 子句可以使用表名（或别名）访问现有行，并使用特殊排除表访问建议插入的行...
