Every developer has that moment of panic — you run an UPDATE or DELETE statement, hit enter, and then immediately realize you forgot the WHERE clause, or got the condition wrong. If you’re working inside a transaction at that moment, ROLLBACK is what saves you. It’s the command that undoes everything you’ve done since the transaction began, as if none of it ever happened.
I want to walk you through exactly how ROLLBACK works in PostgreSQL, when to use it, and some of the details that matter more than people realize once you’re working with real applications instead of just running one-off queries.
What Is ROLLBACK?
ROLLBACK ends the current transaction and discards all the changes made within it. Nothing you did since the transaction started — whether that’s inserts, updates, deletes, or schema changes — gets applied to the database. It’s as though the transaction never happened at all.
This is the counterpart to COMMIT, which ends a transaction by making all its changes permanent. Together, BEGIN, COMMIT, and ROLLBACK form the foundation of transactional control in PostgreSQL, letting you group multiple statements into one atomic unit: either everything succeeds together, or nothing does.
Why ROLLBACK Matters
Databases without proper transaction support (or applications that don’t use transactions correctly) are fragile. A network hiccup, an application crash, or a bug partway through a multi-step operation can leave your data in an inconsistent state — half-updated, with no clean way to recover. Transactions, and the ability to ROLLBACK, protect you from exactly that.
Beyond error recovery, ROLLBACK is also genuinely useful as a deliberate tool:
- Testing changes safely in a live session before committing to them
- Aborting a batch operation partway through if something looks wrong
- Automatically undoing everything when an application-level exception occurs mid-transaction
- Recovering gracefully from constraint violations or serialization failures
Basic Syntax
BEGIN;
-- some statements
ROLLBACK;
That’s really it in its simplest form. PostgreSQL also accepts ROLLBACK WORK; and ROLLBACK TRANSACTION; as equivalent, more verbose synonyms, if you prefer that style or you’re working with SQL that needs to match conventions from other databases.
There’s also ROLLBACK TO SAVEPOINT, which is a related but distinct command for partial rollbacks — I’ll touch on that briefly, though it deserves its own deeper treatment given how much nuance it has.
ROLLBACK TO SAVEPOINT savepoint_name;
This form doesn’t end the transaction — it just undoes everything back to a specific checkpoint within it, letting the transaction continue.
A Basic, Complete Example
Let’s see this in action with psql.
BEGIN;
INSERT INTO products (name, price) VALUES ('Wireless Mouse', 25.99);
SELECT * FROM products WHERE name = 'Wireless Mouse';
-- You'll see the row, even though it's not committed yet
ROLLBACK;
SELECT * FROM products WHERE name = 'Wireless Mouse';
-- The row is gone, as if the INSERT never happened
Notice that within the transaction, before rolling back, the SELECT shows the inserted row — that’s because your own session can always see its own uncommitted changes. But once you issue ROLLBACK, that insert is completely discarded, and the row disappears as though it never existed.
ROLLBACK After an Error
One of the most important things to understand about PostgreSQL transactions is what happens when a statement fails partway through. Unlike some databases that let you keep running subsequent statements after an error, PostgreSQL puts the entire transaction into an “aborted” state the moment any statement inside it fails.
BEGIN;
INSERT INTO accounts (name, balance) VALUES ('Charlie', 100);
UPDATE accounts SET balance = balance / 0 WHERE name = 'Charlie';
-- ERROR: division by zero
SELECT * FROM accounts WHERE name = 'Charlie';
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
-- Now you can start fresh
Once that division-by-zero error hits, PostgreSQL refuses to run any further commands in that transaction — not even a harmless SELECT — until you explicitly issue ROLLBACK (or ROLLBACK TO SAVEPOINT, if you had one set before the failing statement). This is a very deliberate design choice: PostgreSQL won’t let you keep building on top of a transaction that’s already in an inconsistent, error-triggered state.
This is exactly why many application frameworks and ORMs automatically issue a ROLLBACK the moment any exception is raised inside a transaction block — it’s the only way to get the connection back to a usable state.
ROLLBACK in Application Code
Almost every application framework handles this for you, but it’s worth understanding what’s actually happening underneath.
Python (psycopg2):
import psycopg2
conn = psycopg2.connect("dbname=mydb user=myuser")
cur = conn.cursor()
try:
cur.execute("INSERT INTO orders (customer_id, total) VALUES (%s, %s)", (1, 250.00))
cur.execute("UPDATE inventory SET stock = stock - 1 WHERE product_id = %s", (7,))
conn.commit()
except Exception as e:
conn.rollback()
print(f"Transaction failed and was rolled back: {e}")
Node.js (pg):
const client = await pool.connect();
try {
await client.query('BEGIN');
await client.query('INSERT INTO orders (customer_id, total) VALUES ($1, $2)', [1, 250.00]);
await client.query('UPDATE inventory SET stock = stock - 1 WHERE product_id = $1', [7]);
await client.query('COMMIT');
} catch (err) {
await client.query('ROLLBACK');
console.error('Transaction failed and was rolled back:', err);
} finally {
client.release();
}
The pattern is the same across virtually every language: wrap your statements in a try block, commit at the end if everything succeeded, and roll back in the catch/except block if anything went wrong. This is the fundamental building block of safe, consistent database operations in application code.
ROLLBACK TO SAVEPOINT: Partial Rollbacks
While a full ROLLBACK discards the entire transaction, sometimes you only want to undo part of it. That’s where savepoints come in, paired with ROLLBACK TO SAVEPOINT.
BEGIN;
INSERT INTO orders (customer_id, status) VALUES (1, 'pending');
SAVEPOINT before_payment;
UPDATE accounts SET balance = balance - 500 WHERE customer_id = 1;
-- Suppose this triggers a check constraint violation because balance would go negative
ROLLBACK TO SAVEPOINT before_payment;
-- The order insert is still intact, only the failed payment attempt was undone
UPDATE orders SET status = 'payment_failed' WHERE customer_id = 1;
COMMIT;
This gives you a middle ground between “undo everything” and “undo nothing” — genuinely useful for complex, multi-step transactions where you want fine-grained control over what survives a failure.
Common Use Cases for ROLLBACK
- Error recovery in application transactions — the most common use case by far. Any multi-statement operation should be wrapped in a transaction with proper rollback handling on failure.
- Interactive testing in a live session — start a transaction, try a risky change, inspect the results, and roll back if you don’t like what you see, without any permanent effect on the database.
- Aborting batch jobs mid-run — if a batch process detects something seriously wrong partway through (like unexpected data volume or a sanity check failure), rolling back the whole transaction can be safer than trying to undo individual statements manually.
- Serialization failure recovery — under stricter isolation levels (like
SERIALIZABLE), PostgreSQL can abort a transaction due to a detected conflict with another concurrent transaction. The correct response is to roll back and retry the whole transaction. - Database migrations gone wrong — running schema changes inside a transaction (PostgreSQL supports transactional DDL, unlike many other databases) means you can roll back an entire failed migration cleanly.
Troubleshooting Common ROLLBACK Issues
“ERROR: current transaction is aborted, commands ignored until end of transaction block” This means an earlier statement in your transaction failed, and PostgreSQL is refusing further commands until you roll back (fully, or to a savepoint set before the failure). This is expected behavior, not a bug — just issue ROLLBACK and start over, or use savepoints proactively before risky statements so you have a recovery point.
“I rolled back but my sequence values (like SERIAL id) still jumped.” This is a common point of confusion. Sequences used for auto-incrementing columns are not transactional in PostgreSQL — they’re deliberately designed this way for performance, so that concurrent transactions don’t block each other waiting for the next sequence value. If you INSERT a row (consuming a sequence value) and then roll back, that specific ID number is “used up” and won’t be reused, even though the row itself never actually got saved. This is completely normal and not something to worry about — gaps in ID sequences are expected and harmless.
“My application seems to hang after an error, and nothing else runs.” Check whether your connection is stuck in an aborted transaction state, and your application code isn’t issuing a ROLLBACK before trying to run more queries on that same connection. This is a very common bug in hand-rolled transaction handling — always make sure your error-handling path actually calls rollback.
“Can I roll back after COMMIT?” No. Once a transaction commits, it’s permanent — there’s no built-in UNCOMMIT. Your only recovery options at that point are things like restoring from a backup, using point-in-time recovery if you have WAL archiving set up, or manually writing corrective statements (and wrapping those in their own transaction, of course).
Best Practices
- Always wrap multi-statement operations in explicit transactions, and make sure your error handling path actually issues
ROLLBACK— don’t just let connections hang in an aborted state. - Use savepoints for partial rollback needs rather than restructuring your entire transaction around all-or-nothing behavior when that’s not really what you need.
- Don’t worry about sequence gaps caused by rollbacks. They’re a normal, expected side effect of how sequences work, not a sign of data corruption.
- Keep transactions as short as reasonably possible. Long-running transactions that might need to roll back hold locks and resources longer, which can affect concurrent access from other sessions.
- Test your rollback logic, not just your happy path. It’s easy to test that a successful transaction commits correctly and forget to verify that a failed one actually rolls back cleanly, especially in application code with nested try/catch logic.
- Remember that DDL is transactional in PostgreSQL. Unlike MySQL, you can wrap
CREATE TABLE,ALTER TABLE, and other schema changes inside a transaction and roll them back if something goes wrong partway through a migration — this is a genuine advantage worth using deliberately.
Wrapping Up
ROLLBACK is one of the most important safety mechanisms PostgreSQL gives you. It turns “I made a mistake” from a potential disaster into a non-event, as long as you’re working inside a transaction. Whether you’re recovering from an unexpected error in application code, testing something risky in a live session, or cleanly aborting a batch job that’s gone sideways, understanding exactly how and when to use ROLLBACK — including its partial form with savepoints — is fundamental to writing reliable, safe database code.
