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;
I found the answer. The rollback will happen when an error occurs without me doing anything.
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
