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

sql postgresql upsert
MK Yung picture MK Yung · Dec 29, 2015 · Viewed 27k times · Source

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

Answer

MK Yung picture MK Yung · Dec 30, 2015

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

Related questions

Insert, on duplicate update in PostgreSQL?

Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax: INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z) ON DUPLICATE KEY …

Read More
How do I UPDATE a row in a table or INSERT it if it doesn't exist?

I have the following table of counters: CREATE TABLE cache ( key text PRIMARY KEY, generation int ); I would like to increment one of the counters, or set it to zero if the corresponding row doesn't exist yet. Is there a …

Read More
How to use RETURNING with ON CONFLICT in PostgreSQL?

I have the following UPSERT in PostgreSQL 9.5: INSERT INTO chats ("user", "contact", "name") VALUES ($1, $2, $3), ($2, $1, NULL) ON CONFLICT("user", "contact") DO NOTHING RETURNING id; If there are no conflicts it returns something like this: ---------- | id | ---------- 1 | 50 | ---------- 2 | 51 | ---------- But if …

Read More