Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

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;
PRACTICALS FOR 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;
ROLLBACK;
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)
EXAMPLE2:
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)


Comments

Popular posts from this blog

PostgreSQL pgBadger

PostgreSQL Pgbadger Installation On Linux

How To Schedule PostgreSQL Jobs using pgAgent on Linux plateform

How to configure Replication Manager (repmgr) ?

Script to kill ALL IDLE Connection In postgreSQL