Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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
);
like image 985
MK Yung Avatar asked Dec 29 '15 15:12

MK Yung


People also ask

What is needed for an insert on conflict update to work?

You must have INSERT privilege on a table in order to insert into it. If ON CONFLICT DO UPDATE is present, UPDATE privilege on the table is also required. If a column list is specified, you only need INSERT privilege on the listed columns.

What's the fastest way to do a bulk insert into Postgres?

To perform a bulk insert you'd simply createdb and then use csvsql , which connects to your database and creates individual tables for an entire folder of CSVs.

How do you insert if row does not exist Upsert in PostgreSQL?

There is a nice way of doing conditional INSERT in PostgreSQL: INSERT INTO example_table (id, name) SELECT 1, 'John' WHERE NOT EXISTS ( SELECT id FROM example_table WHERE id = 1 );

Does Postgres support Upsert?

PostgreSQL added the ON CONFLICT target action clause to the INSERT statement to support the upsert feature. In this statement, the target can be one of the following: (column_name) – a column name. ON CONSTRAINT constraint_name – where the constraint name could be the name of the UNIQUE constraint.


1 Answers

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

like image 103
MK Yung Avatar answered Oct 28 '22 20:10

MK Yung