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
);
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.
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.
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 );
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.
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...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With