How to use transactions effectively in failing queries in Postgresql

Question

I'm a bit new to transaction in postgresql. I read many tutorials in it but what i want to do is that... If i have many queries for example:

INSERT INTO a (id) VALUES (1)
INSERT INTO a (id) VALUES (2)
INSERT INTO a (id) VALUES (3)
INSERT INTO a (id) VALUES (4)
....

if it failed in the 3rd or any nth query because of some issue, what i want is to rollback all the other previous queries automatically.

My question is, is there any way to do that in postgresql?

What client tool/library are you using to connect to PostgreSQL? Different tools use different method. — jjanes
– jjanes, Commented May 22, 2014 at 16:41

Cjxcz Odjcayrwl · Accepted Answer · 2014-05-22 08:34:36Z

3

If you have a transaction, PostgreSQL will automatically ROLLBACK the whole transaction if any query fails. It doesn't matter if you submit ROLLBACK or COMMIT, rollback will follow. Additionally, any query submited will end with SQL exception.

It's a strict view on transaction concept: all or nothing.

If you don't want such behaviour, you need to use savepoints or plpgsql blocks with EXCEPTION handling.

answered May 22, 2014 at 8:34

Cjxcz Odjcayrwl

23k44 gold badges150 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use transactions effectively in failing queries in Postgresql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related