postgresql rollback example - Brave Search

How to roll back a transaction on error in PostgreSQL?

stackoverflow.com › questions › 63489949 › how-to-roll-back-a-transaction-on-error-in-postgresql

You cannot use ROLLBACK in PL/pgSQL, except in certain limited cases inside procedures.

You don't need to explicitly roll back in your PL/pgSQL code. Just let the exception propagate out of the PL/pgSQL code, and it will cause an error, which will cause the whole transaction to be rolled back.

Your comments suggest that this code is called from an SQL script. Then the solution would be to have a COMMIT in that SQL script at some place after the PL/pgSQL code. That would end the transaction and roll it back.

Answer from Laurenz Albe on Stack Overflow

geeksforgeeks.org › postgresql › postgresql-rollback

PostgreSQL - ROLLBACK - GeeksforGeeks

July 23, 2025 - Explanation: in this example, the changes are reverted to the original state after the ROLLBACK command. PostgreSQL ROLLBACK command is to undo changes made during a transaction.

postgresql.org › docs › 9.4 › sql-rollback.html

PostgreSQL: Documentation: 9.4: ROLLBACK

February 13, 2020 - This documentation is for an unsupported version of PostgreSQL. You may want to view the same page for the current version, or one of the other supported versions listed above instead. ... ROLLBACK rolls back the current transaction and causes all the updates made by the transaction to be discarded.

Videos

How to implement Transactions (COMMIT, ROLLBACK, SavePoint) in ...

November 24, 2022

Transactions in #PostgreSQL #COMMIT #ROLLBACK #SQLTransaction ...

October 30, 2020

PostgreSQL Demonstration: Transactions - YouTube

October 27, 2020

Tutorial 52 - Transactions in PostgreSQL. - YouTube

PostgreSQL - Rollback in Transaction with Best Practice - YouTube

August 29, 2023

Ejemplo de transacción Postgresql en pgAdmin - YouTube

November 17, 2020

postgresql.org › docs › current › sql-rollback-to.html

PostgreSQL: Documentation: 18: ROLLBACK TO SAVEPOINT

May 14, 2026 - BEGIN; DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2; SAVEPOINT foo; FETCH 1 FROM foo; ?column? ---------- 1 ROLLBACK TO SAVEPOINT foo; FETCH 1 FROM foo; ?column? ---------- 2 COMMIT; The SQL standard specifies that the key word SAVEPOINT is mandatory, but PostgreSQL and Oracle allow it to be omitted.

bytebase.com › blog › guides › postgres rollback explained

Postgres Rollback Explained | Bytebase

September 4, 2025 - BEGIN; -- Step 1: safe operations ... 'Marketing'); -- Oops, Bob is actually in Sales -- Roll back only the risky step ROLLBACK TO SAVEPOINT sp_batch; -- Step 3: continue with corrected operation INSERT INTO employees (name, department) ...

github.com › acakojic › postgresql-learning › blob › main › transactions › 1_4_postgresql_rollback_in_transaction.md

postgresql-learning/transactions/1_4_postgresql_rollback_in_transaction.md at main · acakojic/postgresql-learning

Now, let's improve this with error handling and ROLLBACK. DO $$ -- PostgreSQL anonymous code block DECLARE v_error BOOLEAN := FALSE; BEGIN -- Start the transaction BEGIN; -- Debit Account 1 UPDATE accounts SET balance = balance - 100 WHERE id = 1; IF NOT FOUND THEN RAISE 'Account 1 not found'; END IF; -- Credit Account 2 UPDATE accounts SET balance = balance + 100 WHERE id = 2; IF NOT FOUND THEN RAISE 'Account 2 not found'; END IF; -- If we reach this point, it means no error has occurred COMMIT; EXCEPTION WHEN OTHERS THEN -- An error occurred, so rollback the transaction ROLLBACK; v_error := TRUE; END; $$;

Author acakojic

Heroku Dev Center

devcenter.heroku.com › articles › heroku-postgres-rollback

Heroku Postgres Rollback | Heroku Dev Center

May 27, 2026 - Before rolling back your database, check the earliest your can roll back to with data:pg:info and look for the Rollback field: $ heroku data:pg:info HEROKU_POSTGRESQL_COBALT === postgresql-rugged-24211 as HEROKU_POSTGRESQL_COBALT_URL on ⬢ example-app ...

postgresql.org › docs › current › tutorial-transactions.html

PostgreSQL: Documentation: 18: 3.4. Transactions

May 14, 2026 - = 'Bob'; -- oops ... forget that and use Wally's account ROLLBACK TO my_savepoint; UPDATE accounts SET balance = balance + 100.00 WHERE name = 'Wally'; COMMIT; This example is, of course, oversimplified, but there's a lot of control possible ...

enterprisedb.com › postgres-tutorials › how-work-postgresql-transactions

How to work with PostgreSQL transactions | EDB

postgres=# begin; BEGIN postgres=# create table my_table(n int); CREATE TABLE postgres=# insert into my_table values (1); INSERT 0 1 postgres=# savepoint my_savepoint; SAVEPOINT postgres=# insert into my_table values(2); INSERT 0 1 postgres=# insert into my_table values(3); INSERT 0 1 postgres=# select * from my_table; n --- 1 2 3 (3 rows) postgres=# rollback to my_savepoint; ROLLBACK postgres=# select * from my_table; n --- 1 (1 row) postgres=# commit; COMMIT postgres=#

Find elsewhere

Google Bing Mojeek

stackoverflow.com › questions › 63489949 › how-to-roll-back-a-transaction-on-error-in-postgresql

exception - How to roll back a transaction on error in PostgreSQL? - Stack Overflow

You cannot use ROLLBACK in PL/pgSQL, except in certain limited cases inside procedures.

You don't need to explicitly roll back in your PL/pgSQL code. Just let the exception propagate out of the PL/pgSQL code, and it will cause an error, which will cause the whole transaction to be rolled back.

Your comments suggest that this code is called from an SQL script. Then the solution would be to have a COMMIT in that SQL script at some place after the PL/pgSQL code. That would end the transaction and roll it back.

I had this error too when I tried to run my script after it raised an exception:

ERROR: current transaction is aborted, commands ignored until end of transaction block

To fix it, I simply added `ROLLBACK` at the beginning of my script, like this:

ROLLBACK; -- fix "current transaction is aborted, commands ignored until end of transaction block"

BEGIN;
DO
$$
    DECLARE
        v_nb INTEGER := 1;
    BEGIN
        RAISE NOTICE '0 + % = %', v_nb, v_nb;
        RAISE EXCEPTION 'trigger an exception for fun';
    END
$$;

COMMIT;

neon.com › postgresql › postgresql-tutorial › postgresql-transaction

PostgreSQL Transaction

For example, the following example uses the ROLLBACK statement to roll back the changes made to the account 1: -- start a transaction BEGIN; UPDATE accounts SET balance = balance - 1000 WHERE id = 1; -- rollback the changes ROLLBACK; If you retrieve ...

opensource-db.com › home › blog › mastering postgresql: rollback to savepoints !!

Mastering PostgreSQL: Rollback to Savepoints !! - OpenSourceDB

November 22, 2023 - To grasp how to implement rollback to savepoint in PostgreSQL, let’s consider a practical example with our favorite two tables “osdb_dept” and “osdb_emp“, with “osdb_dept” serving as the parent table and linked to “osdb_emp” child table, through the “dept_id” column.

compilenrun.com › postgresql tutorial › postgresql transactions › postgresql rollback

PostgreSQL ROLLBACK | Compile N Run

... BEGIN; INSERT INTO customers (first_name, last_name, email) VALUES ('John', 'Doe', '[email protected]'); -- Oh wait, we made a mistake! ROLLBACK; In this example, the INSERT operation is cancelled and no new record is added to the customers ...

postgresql.org › docs › current › sql-rollback.html

ROLLBACK - PostgreSQL: Documentation: 18

May 14, 2026 - ROLLBACK ROLLBACK — abort the current transaction Synopsis ROLLBACK [ WORK | TRANSACTION ] [ AND [ NO ] CHAIN …

medium.com › nerd-for-tech › transaction-in-postgresql-rollback-savepoint-23987dc6c533

Transaction in PostgreSQL | ROLLBACK | SAVEPOINT | by Sohaib Anser | Nerd For Tech | Medium

July 1, 2021 - Now in the transaction, after 100 gems deduction from David, a savepoint will be marked. Actually, David’s gems will be transferred to Joe but accidentally transferred to John. Next, a Rollback will be established to the marked savepoint, ...

docs.devart.com › dotconnect › postgresql › Devart.Data.PostgreSql~Devart.Data.PostgreSql.PgSqlTransaction~Rollback().html

Rollback() Method - Documentation - Devart

public void RunPgSqlTransaction(string myConnString) { PgSqlConnection pgConnection = new PgSqlConnection(myConnString); pgConnection.Open(); PgSqlCommand pgCommand = new PgSqlCommand(); PgSqlTransaction myTrans; // Start a local transaction myTrans = pgConnection.BeginTransaction(); // Assign transaction object for a pending local transaction pgCommand.Transaction = myTrans; try { pgCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(50, 'DEVELOPMENT')"; pgCommand.ExecuteNonQuery(); pgCommand.CommandText = "INSERT INTO Test.Dept(DeptNo, DName) Values(60, 'PRODUCTION')"; pgCommand.ExecuteNonQuery(); myTrans.Commit(); Console.WriteLine("Both records are written to database."); } catch(Exception e) { myTrans.Rollback(); Console.WriteLine(e.ToString()); Console.WriteLine("Neither record was written to database."); } finally { pgConnection.Close(); } }

tutorialspoint.com › postgresql › postgresql_rollback.htm

PostgreSQL - ROLLBACK

Suppose the payment fails, the stock should not be reduced. ROLLBACK ensures the stock is restored if the transaction is incomplete. Now, please connect to PostgreSQL − COMMIT chapters where we entered the values of one new user.

postgresql.org › docs › 9.1 › sql-rollback.html

PostgreSQL: Documentation: 9.1: ROLLBACK

October 27, 2016 - This documentation is for an unsupported version of PostgreSQL. You may want to view the same page for the current version, or one of the other supported versions listed above instead. ... ROLLBACK rolls back the current transaction and causes all the updates made by the transaction to be discarded.

stackoverflow.com › questions › 61611829 › what-is-the-meaning-of-the-rollback-in-postgresql-and-when-to-use-it

transactions - What is the meaning of the rollback in PostgreSql and when to use it? - Stack Overflow

A transaction can consist of several database statements, and there may be application code interleaved.

Imagine a simple money transfer:

START TRANSACTION;
SELECT amount FROM account WHERE account_nr = 432 FOR UPDATE;
/* do some calculation in the application */
UPDATE account SET amount = 543.23 WHERE account_nr = 432;
SELECT amount FROM account WHERE account_nr = 123 FOR UPDATE;
/* do another calculation in the application */
UPDATE account SET amount = 1903.4 WHERE account_nr = 123;
COMMIT;

Now at any point you could encounter a problem, for example:

The second SELECT statement may return no result, because there is no such account.
The second UPDATE could fail, because it violates a database constraint.
The second calculation in the application may cause an error condition.

If something like that happens, you need to abort the transaction and undo its work. For example, you cannot allow the change to account 432 to become visible.

The proper way to do that in a relational database is to issue a ROLLBACK, then all that will happen automatically.

Transactions are a service for the user to make it easy to write reliable and robust code.

tutorialspoint.com › postgresql › postgresql_transactions.htm

PostgreSQL - TRANSACTIONS

But a transaction will also ROLLBACK if the database is closed or if an error occurs.

postgresql.org › docs › 9.4 › sql-rollback-to.html

PostgreSQL: Documentation: 9.4: ROLLBACK TO SAVEPOINT

February 13, 2020 - BEGIN; DECLARE foo CURSOR FOR SELECT 1 UNION SELECT 2; SAVEPOINT foo; FETCH 1 FROM foo; ?column? ---------- 1 ROLLBACK TO SAVEPOINT foo; FETCH 1 FROM foo; ?column? ---------- 2 COMMIT; The SQL standard specifies that the key word SAVEPOINT is mandatory, but PostgreSQL and Oracle allow it to be omitted.