Gavin Sellers Gavin Sellers - 1 year ago 318
SQL Question

Postgresql Batch insert and on conflict batch update

I'm trying to batch insert rows into a postgres db and on conflict update the conflicted rows. I'm wondering what the best way to do this is. The query I'm currently fails to insert any rows, but if I remove the on conflict, it works perfectly. I'm also not getting any errors from what I can tell.

Here is the current query I'm using:

'INSERT INTO table (x1, x2, x3, ...) VALUES %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,... ON CONFLICT DO UPDATE SET (x4, x5, x6) = %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,...'

I have a function which populates the %s values with tuples of the form (x1, x2, ...)

My table looks like this

Table "public.table"
Column | Type | Modifiers

id | integer | not null default nextval('table_id_seq'::regclass)
x1 | text | not null
x2 | text | not null
x3 | integer | not null
x4 | text | not null
x5 | text | not null
x6 | text | not null
"table_feature_pkey" PRIMARY KEY, btree (id)

Thanks in advance. Let me know if you need additional info.

Answer Source

You are going to use obviously incorrect syntax. Having the table

create table a_table(id serial primary key, x1 int, x2 int);

try this in psql

insert into a_table (x1, x2) 
values (1,2), (3,4)
on conflict do
update set (x1, x2) = (1,2), (3,4);

to get

ERROR:  syntax error at or near "3"
LINE 4:  update set (x1, x2) = (1,2), (3,4);

On the other hand, ON CONFLICT makes no sense in this case. A conflict will never happen, as none of the used columns (or group of columns) is unique.

Check INSERT syntax, read more about UPSERT in wiki.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download