Trap exception in PostgreSQL script

Question

I want to run a script inside a transaction in PostgreSQL. So I surround the SQL code with begin and commit statements. But I want to rollback on error. I don't see how to do that.

BEGIN;
UPDATE public.tablename
SET blah = 'xxx'
WHERE thing= '123';
COMMIT;

Answer 1

I found the answer. The rollback will happen when an error occurs without me doing anything.

Answer 2

You set a savepoint and after the comand a Rollback

CREATE tABLE tablename (blah varchar(3), thing varchar(3))

BEGIN;
SAVEPOINT my_savepoint;
 UPDATE tablename
 SET blah = 'xxx'
 WHERE thing= '123';
ROLLBACK TO my_savepoint;
COMMIT;

db<>fiddle here

• Peter Marshall: Yes: That's nice, I can test my script that way, Thank you.