我正在编写一个数据挖掘程序,批量插入用户数据.
当前的sql只是一个普通的批量插入：
insert into USERS(
id,username,profile_picture)
select unnest(array['12345']),unnest(array['Peter']),unnest(array['someURL']),on conflict (id) do nothing;
如果冲突,我该如何进行更新？我试过了：
...
unnest(array['Peter']) as a,unnest(array['someURL']) as b,on conflict (id) do
update set
username = a,profile_picture = b;
但是它抛出在表“* SELECT *”中有一个名为“a”的列,但是不能从查询的这一部分引用它.错误.
编辑：
USERS表非常简单：
create table USERS (
id text not null primary key,username text,profile_picture text
);
解决方法
结果是一个名为excluded的特殊表包含要插入的行
(奇怪的名字)
(奇怪的名字)
insert into USERS(
id,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
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…
