PostgreSQL DBMS is used to manage data by creating tables in the databases and attaching them by creating relationships between them. Accessing the data which can be useful for the consumer is the major aspect of creating databases. Manipulating tables to update values stored in them can be helpful to get useful insights by applying queries. Transactions are utilized in queries to fetch data from PostgreSQL tables and perform updates on them securely and reliably.

This guide will discuss the use of PostgreSQL transactions.

How to Use PostgreSQL Transaction?

Transactions are allowed to be used in the queries to fetch data from the PostgreSQL tables and make updates to them. A database Transaction is a unit of work containing multiple steps to be performed all at once or none at all. It means that all steps in the transaction will be performed or if there occurs a problem during the transaction, it will revoke all the previous steps and start from the beginning.

Features of Transaction

A database transaction contains 4 major features which are referred to as ACID:

Atmocity: It guarantees that the steps involved in the transaction must be completed in an all-or-nothing method.

Consistency: Consistency refers to the updation or alteration of the data of the database that should be performed according to predefined rules.

Isolation: Isolation means that if there are multiple transactions happening in the database won't bother each other or have their own privacy rules.

Durability: It makes sure that the committed transaction is stored in the database permanently and not just for the transaction period.

Syntax

The following is the syntax for the transaction in the PostgreSQL database:

BEGIN;
   statements
   [COMMIT | ROLLBACK];

Here in the above syntax:

- The transaction starts with the BEGIN keyword that marks the transaction.
- Write some statements in the query to be performed inside the transaction which refers to the body of the transaction.
- It ends with COMMIT or ROLLBACK that is used to confirm the transaction or revoke it respectively.

Example 1: Starting PostgreSQL Transaction

The following query is used to start the transaction to insert values in the table:

BEGIN; 
INSERT INTO trxn(name,balance)
 VALUES('Alice',10000);

Running the above command starts the transaction to insert a row containing name and balance fields:

Start a new session and run the following command:

SELECT * FROM trxn;

The above command is supposed to print the data inserted in the table in the previous step but it shows nothing in the table. It is because the transaction is not committed which is used to update the changes permanently:

Example 2: Committing PostgreSQL Transaction

This example will use COMMIT to insert data on the PostgreSQL table:

BEGIN;
INSERT INTO trxn(name,balance)
 VALUES('Alice',10000);
 COMMIT;

The above code starts a transaction and runs the query containing a statement to insert data in the PostgreSQL table. After that, the query ends with the COMMIT keyword to confirm the changes made through the transaction:

Use the following query to check the data inserted in the PostgreSQL table:

SELECT * FROM trxn;

Running the above code will display the data inserted through the transaction:

Example 3: RollBack in PostgreSQL Transaction

The next example will use the ROLLBACK clause in the transaction to revoke the changes made through the transaction. Use the following query to check the data available in the PostgreSQL table:

SELECT * FROM trxn;

Running the above query displays that there are multiple rows inserted in the table:

Use the following code to start a transaction and then apply changes to the table but revoke them at the end:

BEGIN;
UPDATE trxn 
 SET balance = balance - 1500
 WHERE id = 2;
 UPDATE trxn
 SET balance = balance + 1500
 WHERE id = 4; 
 ROLLBACK;

This code will subtract the balance from id 2 and add it to the balance of id 4 on the “trxn” table. At the end of the query, the ROLLBACK keywords are used to reverse the updation to the table:

Use the following code to check if there is any change occurred through the above transaction:

SELECT * FROM trxn;

The following screenshot displays that the transaction has made no change to the PostgreSQL table:

That’s all about using transactions in PostgreSQL.

Conclusion

A database transaction in PostgreSQL acts as a unit containing multiple steps in it to be performed at once or none at all. The syntax of the database transaction in PostgreSQL contains the BEGIN keyword marking the start of the transaction. It then contains statements of the query and ends with the COMMIT or ROLLBACK clauses used to confirm or reverse the changes respectively. This guide has demonstrated the use of PostgreSQL transactions with examples.