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

Oracle DBMS SCHEDULER Examples

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

7 Steps to configure BDR replication in postgresql

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

vacuumlo - removing large objects orphans from a database PostgreSQL