PostgreSQL – ROLLBACK

PostgreSQL ROLLBACK command is used to undo the changes done in transactions. As we know transactions in database languages are used for purpose of large computations, for example in banks. For suppose, the employee of the bank incremented the balance record of the wrong person mistakenly then he can simply rollback and can go to the previous state.

Syntax:

ROLLBACK TRANSACTION;

(or)

ROLLBACK;

(or)

ROLLBACK WORK;

To understand the importance of the ROLLBACK command first let’s 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:  

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;
        
ROLLBACK;

SELECT * FROM BankStatements;

Output:

Example 2:

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: