PostgreSQL Rollback

The ROLLBACK Command:

• The ROLLBACK command is the transactional command used to undo transactions that have not already been saved to the database.
• The ROLLBACK command can only be used to undo transactions since the last COMMIT or ROLLBACK command was issued.

The syntax for ROLLBACK command is as follows:

ROLLBACK;
EXAMPLE 1: Consider EMP table is having the following records:

postgres=# select * from emp;
 id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

–Now, let’s start a transaction and delete records from the table having age = 25 and finally we use ROLLBACK command to undo all the changes.

postgres=# BEGIN;
DELETE FROM EMP WHERE AGE = 25;
COMMIT;

If you will check EMP table is still having the following records:

id | name  | age | address   | salary
----+-------+-----+-----------+--------
  1 | Paul  |  32 | California|  20000
  2 | Allen |  25 | Texas     |  15000
  3 | Teddy |  23 | Norway    |  20000
  4 | Mark  |  25 | Rich-Mond |  65000
  5 | David |  27 | Texas     |  85000
  6 | Kim   |  22 | South-Hall|  45000
  7 | James |  24 | Houston   |  10000

–Now, let’s start another transaction and delete records from the table having age = 25 and finally we use COMMIT command to commit all the changes.

postgres=# BEGIN;
DELETE FROM EMP WHERE AGE = 25;
COMMIT;

If you will check EMP table is still having the following records:

id | name  | age | address    | salary
----+-------+-----+------------+--------
  1 | Paul  |  32 | California |  20000
  3 | Teddy |  23 | Norway     |  20000
  5 | David |  27 | Texas      |  85000
  6 | Kim   |  22 | South-Hall |  45000
  7 | James |  24 | Houston    |  10000
(5 rows)

EXAMPLE 2:
let’s start a transaction and UPDATE records from the table having PLACE = NEWYORK and finally we use ROLLBACK command to undo all the changes.

postgres=# begin;                          
BEGIN

postgres=# SELECT * FROM DEMO WHERE ID = 9;
 id | name  |  place  | pincode 
----+-------+---------+---------
  9 | mohad | pulicat |  601202
(1 row)

postgres=# UPDATE DEMO SET PLACE = 'NEWYORK' WHERE id = 9;
UPDATE 1

postgres=# SELECT * FROM DEMO WHERE id = 9;
 id | name  |  place  | pincode 
----+-------+---------+---------
  9 | mohad | NEWYORK |  601202
(1 row)

postgres=# ROLLBACK;
ROLLBACK

postgres=# SELECT * FROM DEMO WHERE id = 9;
 id | name  |  place  | pincode 
----+-------+---------+---------
  9 | mohad | pulicat |  601202
(1 row)