This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Today morning, one of our Associate DB Developer working with Transactions and PostgreSQL is also new for him.
The COMMIT, ROLLBACK and SAVEPOINT are very common for all RDBMS.
In this post, I am sharing one basic demonstration on COMMIT, ROLLBACK and SAVEPOINT of PostgreSQL which helps to Associate level DB Developer.
Create a table with Sample Data:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE tbl_Employees ( EmpID INT ,EmpName CHARACTER VARYING ); INSERT INTO tbl_Employees VALUES (1,'Anvesh'),(2,'Loother'),(3,'Roy'),(4,'Martin') ,(5,'Jenny'),(6,'Monika'),(7,'Rajesh'),(8,'Eric'); |
Commit: We can use COMMIT to end our all transactions and make It as permanent changes.
Commit the Transaction:
|
1 2 3 4 5 |
BEGIN; UPDATE tbl_Employees SET EmpName = 'Nivu' WHERE EmpID = 3; COMMIT; |
Rollback: Using ROLLBACK, we can jump to the previous last committed state of the Transaction.
Rollback the Transaction:
|
1 2 3 4 5 |
BEGIN; UPDATE tbl_Employees SET EmpName = 'Vinay' WHERE EmpID = 2; ROLLBACK; |
Savepoint: Using SAVEPOINT, we can jump to the particular point which defines by the SAVEPOINT.
Rollback changes to the Save Point:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
BEGIN; UPDATE tbl_Employees SET EmpName = 'Grace' WHERE EmpID = 4; SAVEPOINT EmpSave; UPDATE tbl_Employees SET EmpName = 'Kunal' WHERE EmpID = 5; ROLLBACK TO EmpSave; |
The Result:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT * FROM tbl_Employees ORDER BY 1 ; EmpID EmpName -------------------- 1 Anvesh 2 Loother 3 Nivu 4 Grace 5 Jenny 6 Monika 7 Rajesh 8 Eric |
