EDUCBA

Home Data Science Data Science Tutorials PostgreSQL Tutorial PostgreSQL Transaction

PostgreSQL Transaction

Priya Pedamkar
Article byPriya Pedamkar

Updated May 11, 2023

PostgreSQL Transaction

 

 

Introduction to PostgreSQL Transaction

PostgreSQL transaction handle using the commit, begin, and rollback statements; PostgreSQL database transaction is a unit of work that consists the one or more statements. An example of a complete transaction transfers money from one bank account to another or withdraws money from an ATM; a complete transaction in any database consists of debiting money from one account and successfully crediting it to another. PostgreSQL transaction is ACID (Atomicity, Consistency, Isolation, and Durability) compliant, Transaction in PostgreSQL is fully ACID compliant. Transaction in any database consists of one or more statement which executes as per order.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

How does Transaction work in PostgreSQL?

Below is the working of the transaction statement.

1. There are different use or work of each statement in PostgreSQL. Below are the properties of transactions.

  • Atomicity
  • Consistency
  • Isolation
  • Durability

2. Atomicity consists of operations we have performed on the database that is fully completed or not completed. If the transactions fail in the middle, the Transaction is rollback up to the last save point.

3. Consistency properties in PostgreSQL define as the database properly changing its state of transactions up to the last transactions savepoint. At the time of working on transactions, consistency of transactions is more important.

4. In PostgreSQL, isolation is defined as the ability to enable transaction operations to complete and operate independently. Also, the running Transaction active on the server is transparent to each other.

5. In PostgreSQL, durability is defined as the need to ensure that the results of database operations are not lost in the event of failure. Durability is the most important property of transactions in PostgreSQL.

6. Transaction in PostgreSQL defines the propagation of one or more changes we have performed on the database.

7. We define a PostgreSQL database transaction as an operation that involves inserting records into a table, deleting rows from a table, or updating rows.

8. A transaction can be single updation, insertion, or deletion, or multiple updation, deletion, or insertion statements.

9. When performing a transaction on the database, it is essential to control it to ensure successful completion. We also need to handle any errors that occur in the database during the Transaction.

10. We can club the number of queries in a single set, and after creating a set, we can execute it one by one in single transactions.

11. The Transaction is very important and useful in every database and is the PostgreSQL database’s fundamental concept.

12. Begin, commit, rollback, and savepoint are the transaction control commands we have used in PostgreSQL.

13. Begin command in PostgreSQL is defined as the start of the Transaction. We can start the Transaction using begin keyword in PostgreSQL. We can also start the Transaction using the begin transaction statements.

14. Commit command is used to save the Transaction we executed on the database. We have to use the commit keyword to save the Transaction in PostgreSQL.

15. We can also use the end transaction command to commit the Transaction executed on the database server.

16. Rollback command is used to roll back the Transaction to a specific point. Rollback is an essential and useful command of transaction control in PostgreSQL.

17. We define a save point as a way to partially roll back a transaction that has been performed on the database.

18. Transaction control statements in PostgreSQL will be used with only DML (Data manipulation language) commands. DML commands are inserted, update, and delete. A transaction control command is not used with creating and dropping the database or tables.

19. The system automatically commits creating and dropping operations to the database, eliminating the need to commit the Transaction every time.

20. We cannot roll back the same after successfully committing the Transaction. To roll back the Transaction, we need to set the auto commit off on the database.

21. In PostgreSQL default setting of the autocommit command is ON. The below example shows that the default setting of the autocommit command is as follows.

Query:

\echo :AUTOCOMMIT

Output:

PostgreSQL Transaction Example 1

PostgreSQL Transaction Statements

The following are the transaction statements used in PostgreSQL.

  • Begin
  • Commit
  • Rollback

1. Begin

  • Begin statement is a transaction statement used to start a new transaction. To start a new transaction, we have using begin statements in PostgreSQL.
  • Below is the syntax of the begin statement in PostgreSQL.

Syntax:

1. Begin OR

2. Begin Transaction OR

3. Begin work

  • The above syntax is the same work while using begin Transaction or begins work.
  • Below is an example of a begin statement in PostgreSQL. We have inserted a statement after the beginning statement.
  • The insert statement is successfully executed only after we execute the beginning statement.

Example

Query:

BEGIN Transaction;
INSERT INTO tran_test (id, first_name, last_name, phone, address) VALUES (1, 'ABC', 'PQR', 1234567890, 'Mumbai');
END Transaction;

Output:

PostgreSQL Transaction Example 1

2. Commit

  • Commit command in PostgreSQL is very important to save the Transaction into the database server.
  • Below is the syntax of the commit statement in PostgreSQL.

Syntax:

1. Commit OR

2. Commit Transaction OR

3. Commit work

  • The above syntax is the same work while using commit transactions or commits work.
  • Below is an example of a commit statement in PostgreSQL. We have to insert two statements into the database; after inserting, we have committed the same on the database.

Example 

Query:

INSERT INTO tran_test (id, first_name, last_name, phone, address) VALUES (2, 'PQR', 'XYZ', 1234567890, 'Pune');
INSERT INTO tran_test (id, first_name, last_name, phone, address) VALUES (3, 'ABC', 'XYZ', 1234567890, 'Mumbai');
commit;

Output:

Commit Example 2

3. Rollback

  • Rollback is used to roll back the Transaction from a specific point. Below is the syntax of the rollback statement in PostgreSQL.

Syntax:

1. Rollback OR

2. Rollback transaction OR

3. Rollback work

Example 

In the above example, we have inserted the below statement into the table, and the same statement is rolledback after inserting.

Query:

INSERT INTO tran_test (id, first_name, last_name, phone, address) VALUES (101, 'AB', 'CD', 1234567890, 'Delhi');
Rollback;
Select * from tran_test;

Output:

Rollback  Example 3

Recommended Articles

We hope that this EDUCBA information on “PostgreSQL Transaction” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. How to Notify Works in PostgreSQL?
  2. Introduction to PostgreSQL Timestamp
  3. PostgreSQL NOW() | How to Work?
  4. PostgreSQL RANDOM | Examples