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

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

PostgreSQL Pgbadger Installation On Linux

PostgreSQL pgBadger

How to configure Replication Manager (repmgr) ?

Migrating From Oracle to PostgreSQL using ora2pg open source tools