Up to this point, we have looked at transactions as single units that start with a BEGIN and end with the COMMIT statement with multiple SQL commands in between that are executed at once. Remember as well that without the BEGIN command, each individual SQL statement is viewed as a transaction with an implicit BEGIN and COMMIT command executed. However, you can split this up to execute the commands one at a time and control the results as if they were in a transaction to keep the ACID properties.
To start a transaction in the PostgreSQL command line, you can start with either:
This will start the transaction until the next COMMIT or ROLLBACK command is encountered. However, if there is an error in any of the statements after the BEGIN statement, the changes will automatically be rolled back.
The COMMIT command is used to save changes from a transaction to the database. This COMMIT command will save all of the SQL statements to the database since the BEGIN command. The syntax for the COMMIT command looks like this:
The ROLLBACK command is one that is used to undo or revert SQL Statements that have not been already saved to the database. The ROLLBACK command can only be used to undo SQL statements since the BEGIN command. The syntax looks like this:
Let us take a look at an example of a series of statements:
SELECT * FROM customer ORDER BY customer_id;
Let us start our transaction:
Let us go ahead to update the CUSTOMER table to set the customer_id equal to 1 to have the first name set to Bob:
UPDATE customer SET first_name = 'Bob';
Oops, we accidentally updated all of the names to Bob as we forgot the WHERE clause.
SELECT * FROM customer;
We can use the ROLLBACK statement to undo the changes:
So although the database did not throw any errors, we can revert the changes from the last BEGIN statement through the use of the ROLLBACK statement.
Source: Authored by Vincent Tran