sql - query - Inserción masiva, actualizar si está en conflicto(aumento de volumen) en Postgres

upsert on conflict

Question

Estoy escribiendo un programa de extracción de datos, que inserta de forma masiva los datos del usuario.

El SQL actual es solo una inserción masiva simple:

insert into USERS(
    id, username, profile_picture)
select unnest(array[''12345'']),
    unnest(array[''Peter'']),
    unnest(array[''someURL'']),
on conflict (id) do nothing;

¿Cómo hago una actualización en caso de conflicto? Lo intenté:

...
    unnest(array[''Peter'']) as a,
    unnest(array[''someURL'']) as b,
on conflict (id) do 
update set
    username = a,
    profile_picture = b;

Pero se lanza. There is a column named "a" in table "*SELECT*", but it cannot be referenced from this part of the query. error.

EDITAR :

La tabla de USERS es muy sencilla:

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

Answer 1

Resulta que una tabla especial denominada excluded contiene la fila que se insertará (aunque el nombre es extraño)

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

Las cláusulas SET y WHERE en ON CONFLICT DO UPDATE tienen acceso a la fila existente utilizando el nombre de la tabla (o un alias), y a las filas propuestas para inserción utilizando la tabla especial excluida ...