How to Use Rollback in PostgreSQL: Syntax and Examples

In PostgreSQL, use the ROLLBACK command to undo all changes made in the current transaction. This cancels the transaction and returns the database to its previous state before the transaction began.

Syntax

The ROLLBACK command is used to cancel the current transaction and undo all changes made during it. It is typically used after BEGIN to start a transaction.

• BEGIN; — starts a new transaction.
• ROLLBACK; — cancels the transaction and undoes changes.
• COMMIT; — saves the changes permanently (not part of rollback but related).

sql

BEGIN;
-- perform some SQL operations
ROLLBACK;

Example

This example shows how to start a transaction, insert a row, then rollback to undo the insert so the row is not saved.

sql

BEGIN;
INSERT INTO employees (id, name) VALUES (1, 'Alice');
ROLLBACK;

SELECT * FROM employees WHERE id = 1;

Output

 id | name 
----+-------
(0 rows)

Common Pitfalls

One common mistake is forgetting to use BEGIN before ROLLBACK, which causes an error because there is no active transaction to rollback. Another is expecting ROLLBACK to undo changes after a COMMIT, but once committed, changes cannot be rolled back.

sql

/* Wrong: rollback without begin */
ROLLBACK;

/* Right: rollback inside a transaction */
BEGIN;
-- some changes
ROLLBACK;

Quick Reference

Command	Description
BEGIN;	Start a new transaction
COMMIT;	Save all changes made in the transaction
ROLLBACK;	Undo all changes made in the transaction

Key Takeaways

Use ROLLBACK to undo all changes in the current transaction.

Always start a transaction with BEGIN before using ROLLBACK.

Changes cannot be rolled back after a COMMIT.

Rollback helps keep your database consistent by canceling unwanted changes.

Use rollback carefully to avoid losing important data changes.