CODEWITHSUNDEEP
sqlpostgresqltransactionsatomicupsert
Sidharth Suresh
Sidharth Suresh

Reputation: 11

Is it possible to have multiple upsert operations in Postgres that are atomic?

Similar to having multiple records inserted in one SQL statement, can multiple Upserts be executed atomically?

e.g

insert into students ("name","age") values ("sid",23) on conflict ("name") set "age"=12;
insert into students ("name","age") values ("jack",24) on conflict ("name") set "age"=14;
insert into students ("name","age") values ("tom",20) on conflict ("name") set "age"=13;

Upvotes: 1

Views: 1304

Answers (2)

clemens
clemens

Reputation: 17711

According to the documentation following should work:

INSERT INTO students ("name", "age") VALUES ("sid", 23), ("jack",24), ("tom",20)
ON CONFLICT ("name") DO UPDATE SET "age" = EXCLUDED.age;

Upvotes: 3

Chris Travers
Chris Travers

Reputation: 26454

Yes. Surround with a begin and commit statements, like this:

begin;
insert into students ("name","age") values ("sid",23) on conflict ("name") do update set "age"=12;
insert into students ("name","age") values ("jack",24) on conflict ("name") do update set "age"=14;
insert into students ("name","age") values ("tom",20) on conflict ("name") do update set "age"=13;
commit;

These then constitute a transaction. Some language drivers may have various ways of handling the begin and commit statements so please refer to your driver's documentation before proceeding if you are not just trying to use psql.

But there is no difference between an insert and an upsert in this regard.

Upvotes: 0

Related Questions