Bulk insert, update if on conflict (bulk upsert) on Postgres

Question

I am writing a data-mining program, which bulk inserts user data.

The current SQL is just a plain bulk insert:

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.

EDIT:

Table of USERS is very simple:

create table USERS (
    id      text not null primary key,
    username    text,
    profile_picture text
);

Which is the primary key? What is the table creation code? – serv-inc Dec 29 '15 at 16:12 — serv-inc, Dec 29 '15 at 16:12
@user I have added the code, it is just a very simple table – MK Yung Dec 30 '15 at 05:19 — MK Yung, Dec 30 '15 at 05:19

score 118 · Accepted Answer · edited Dec 22 '16 at 04:33

Turns out a special table named excluded contains the row-to-be-inserted (strange name though)

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

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...

That naming is so strange, I was really confused by the excluded part. Thanks for clarifying. — adnan, Jun 08 '17 at 21:18

score 1 · Answer 2 · answered Apr 20 '22 at 09:22

For bulk insert from another table if they are identical you can do it like that :

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,
......  ;

Bulk insert, update if on conflict (bulk upsert) on Postgres

2 Answers2