Postgres上的批量插入，冲突时更新（批量upsert）

Question

我正在编写一个数据挖掘程序，可以批量插入用户数据。

当前的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
);

- MK Yung

---

哪个是主键？表的创建代码是什么？ - serv-inc

@user，我已经添加了代码，它只是一个非常简单的表格。 - MK Yung

Answer 1

原来有一张特殊的表格叫做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

在ON CONFLICT DO UPDATE中，SET和WHERE子句可以通过表名（或别名）访问现有行，并使用特殊的excluded表访问插入操作中提议的行...

- MK Yung

---

那个命名太奇怪了，被排除掉的部分让我真的很困惑。谢谢你澄清了。 - adnan

Answer 2

如果要从另一个表中批量插入相同的数据，可以像这样操作：

INSERT INTO table_a (SELECT * FROM table_b)
ON CONFLICT ON CONSTRAINT "pk_guid"
DO UPDATE SET
column1 = excluded.column1, 
column2 = excluded.column2,
column3 = excluded.column3,
......  ;

- moaaz salem