CODEWITHSUNDEEP
sqlpostgresql
Gavin Sellers
Gavin Sellers

Reputation: 664

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 Indexes: "table_feature_pkey" PRIMARY KEY, btree (id)

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

Upvotes: 2

Views: 8654

Answers (2)

liyuhui
liyuhui

Reputation: 1270

1.before insert

enter image description here

2.command

enter image description here

3.after insert

enter image description here

Upvotes: 4

klin
klin

Reputation: 121774

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.

Upvotes: 2

Related Questions