PostgreSQL – ROLLBACK

Last Updated : 14 Oct, 2024

The PostgreSQL rollback command is a tool for maintaining data integrity within PostgreSQL databases. This command allows users to undo changes made during a transaction and ensuring that any errors or unexpected issues do not compromise the databases accuracy.

By utilizing the rollback transaction in PostgreSQL, you can easily revert to a previous state when needed. In this article, We will learn practical PostgreSQL rollback examples and explain how to rollback in PostgreSQL.

What is ROLLBACK in PostgreSQL?

In PostgreSQL, ROLLBACK is a command used to undo all changes made during a transaction.
If we start a transaction and perform several operations (like inserting or updating data) but something goes wrong then the ROLLBACK will cancel all those changes and bring the database back to its previous state.
It’s like hitting an “undo” button, ensuring that no partial or incorrect data is saved.

Importance of Transactions and PostgreSQL ROLLBACK

Transactions group multiple operations into one unit, ensuring that either all the changes succeed or none are applied.
This is important to maintain data accuracy and integrity.
The ROLLBACK command is essential because it allows you to discard all changes made in a transaction if there’s an error, preventing incorrect or incomplete data from being committed to the database.
It ensures your database stays consistent even when something goes wrong.

How `ROLLBACK` Works in PostgreSQL?

When we start a transaction using BEGIN, all subsequent operations are kept in a temporary state. If everything goes well, you can save the changes using COMMIT. However, if there’s an issue we can use ROLLBACK to undo all the operations made during the transaction.

After ROLLBACK, the database returns to the state it was in before the transaction started, and no changes are applied. This helps prevent errors and keeps our data clean.

Example of PostgreSQL ROLLBACK

To understand the importance of the ROLLBACK command in PostgreSQL , let’s first build a table for examples.

CREATE TABLE BankStatements (
    customer_id serial PRIMARY KEY,
    full_name VARCHAR NOT NULL,
    balance INT
);

Now we will insert data of some customers

INSERT INTO BankStatements (
    customer_id ,
    full_name,
    balance
)
VALUES
    (1, 'Sekhar rao', 1000),
    (2, 'Abishek Yadav', 500),
    (3, 'Srinivas Goud', 1000);

Now as the table is ready we will understand about commit.

Example 1: Using COMMIT in a Transaction

We will add the data to the table in the transaction using the COMMIT

BEGIN;

 INSERT INTO BankStatements (
     customer_id,
    full_name,
        balance
)
    VALUES 
    ( 4, 'Priya chetri', 500 );

SELECT * FROM BankStatements;
        
COMMIT;

SELECT * FROM BankStatements;

Output:

Example 1 Output

Explanation: In this example, the changes are permanently saved to the database after the COMMIT command.

Example 2: Using ROLLBACK in a Transaction

In this example, we’ll update the balances of two customers and then use the ROLLBACK command to undo the changes.

BEGIN;

      UPDATE BankStatements
      SET balance = balance - 500
      WHERE 
      customer_id = 1;
      
      -- displaying data before
      -- committing the transaction
      SELECT customer_id, full_name, balance
      FROM BankStatements;
      
      UPDATE BankStatements
      SET balance = balance + 500
      WHERE 
      customer_id = 2;
       
ROLLBACK;

-- displaying data after
-- committing the transaction
SELECT customer_id, full_name, balance
FROM BankStatements;

Output:

Explanation: in this example, the changes are reverted to the original state after the ROLLBACK command.

Important Points about ROLLBACK command in PostgreSQL

PostgreSQL ROLLBACK command is to undo changes made during a transaction. If a transaction encounters an error or needs to be reverted for any reason, ROLLBACK can restore the database to its state before the transaction began.
Always use transactions (BEGIN, COMMIT, ROLLBACK) when performing multiple related operations on a database.
Test your transactions thoroughly to ensure that they behave as expected in both COMMIT and ROLLBACK scenarios.
ROLLBACK helps maintain data integrity by ensuring that only fully successful transactions are applied to the database.

Conclusion

In conclusion, understanding the PostgreSQL rollback command is essential for effective database management. It allow users to maintain data consistency by allowing for the reversion of unwanted changes made during transactions. Mastering how to rollback in PostgreSQL can significantly enhance the reliability of your database operations. With clear PostgreSQL rollback examples provided, users can confidently implement these commands to ensure the accuracy and integrity of their data while effectively handling errors and maintaining a stable database environment.