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

SqlPostgresqlUpsert

Sql Problem Overview


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
);

Sql Solutions


Solution 1 - Sql

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

Solution 2 - Sql

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

Attributions

All content for this solution is sourced from the original question on Stackoverflow.

The content on this page is licensed under the Attribution-ShareAlike 4.0 International (CC BY-SA 4.0) license.

Content TypeOriginal AuthorOriginal Content on Stackoverflow
QuestionMK YungView Question on Stackoverflow
Solution 1 - SqlMK YungView Answer on Stackoverflow
Solution 2 - Sqlmoaaz salemView Answer on Stackoverflow