PostgreSQL – ROLLBACK Statement

The ROLLBACK statement is used to undo all changes made within the current transaction and discard those changes. It effectively cancels the transaction and restores the database to its state before the transaction began.

Here’s how you typically use the ROLLBACK statement in PostgreSQL:

Start a transaction using the BEGIN statement:

BEGIN; 

This begins a new transaction.

Perform one or more SQL operations (e.g., INSERT, UPDATE, DELETE, etc.) within the transaction.

If at any point you want to discard the changes made in the transaction and cancel it, issue the ROLLBACK statement:

ROLLBACK;

This will undo all the changes made within the transaction, and the database will return to its state before the transaction started.

Here’s an example of using ROLLBACK within a transaction:

BEGIN; 
-- Perform some operations within the transaction 

INSERT INTO employees (name, salary) VALUES ('John', 50000); 

UPDATE departments SET manager_id = 1 WHERE department_name = 'HR'; 

-- Something went wrong, let's cancel the transaction 
ROLLBACK;

In this example, a transaction is started with BEGIN, and then some database operations are performed. If something goes wrong or if you decide to discard the changes for any reason, the ROLLBACK statement is used to cancel the transaction, and the database returns to its original state.

It’s important to note that rolling back a transaction will undo all changes made within that transaction, so use it carefully when necessary to maintain data integrity and consistency.