1. Home
  2. Docs
  3. Oracle to PostgreSQL Migration
  4. 8 Transaction Handling
  5. 8.1 Transaction Overview

8.1 Transaction Overview

A transaction is a logical unit of work that contains one or more SQL statements. A transaction is an atomic unit which means, the effects of all the SQL statements in a transaction can be either all committed (applied to the database) or all rolled back (undone from the database).

Creating a transaction

OraclePostgreSQL

In Oracle database, a transaction is automatically started when you execute a DML statement. A transaction ends when it is committed or rolled back, either explicitly with a COMMIT or ROLLBACK statement or implicitly when a DDL statement is issued.

SQL> SELECT * FROM product_categories;

CATEGORY_ID CATEGORY_NAME
----------- --------------------
	  2 Television
	  3 Alexa
	  4 Washing Machine
	  5 Laptop
	  1 Air Cooler

SQL> INSERT INTO product_categories(category_id,category_name)
VALUES(6,'Refrigerator');

1 row created.

SQL> INSERT INTO product_categories(category_id,category_name)
VALUES(7,'Water Purifiers');

1 row created.

SQL> SELECT * FROM product_categories;

CATEGORY_ID CATEGORY_NAME
----------- --------------------
	  2 Television
	  3 Alexa
	  4 Washing Machine
	  5 Laptop
	  1 Air Cooler
	  6 Refrigerator
	  7 Water Purifiers

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM product_categories;

CATEGORY_ID CATEGORY_NAME
----------- --------------------
	  2 Television
	  3 Alexa
	  4 Washing Machine
	  5 Laptop
	  1 Air Cooler


SQL> INSERT INTO product_categories(category_id,category_name)
VALUES(6,'Refrigerator');

1 row created.

SQL> INSERT INTO product_categories(category_id,category_name)
VALUES(7,'Water Purifiers');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM product_categories;

CATEGORY_ID CATEGORY_NAME
----------- --------------------
	  2 Television
	  3 Alexa
	  4 Washing Machine
	  5 Laptop
	  1 Air Cooler
	  6 Refrigerator
	  7 Water Purifiers

PostgreSQL by default is auto-commit. So, when you run a DML statement it is automatically committed if the statement is executed successfully or rolled back if encountered any error.

# SELECT * FROM product_categories;

 category_id |  category_name  
-------------+-----------------
           1 | Air Cooler
           2 | Television
           3 | Alexa
           4 | Washing Machine
           5 | Laptop
(5 rows)

# INSERT INTO product_categories(category_id,category_name)
VALUES(6,'Refrigerator');

INSERT 0 1

# ROLLBACK;

WARNING:  there is no transaction in progress
ROLLBACK

# SELECT * FROM product_categories;

 category_id |  category_name  
-------------+-----------------
           1 | Air Cooler
           2 | Television
           3 | Alexa
           4 | Washing Machine
           5 | Laptop
           6 | Refrigerator
(6 rows)

# INSERT INTO product_categories(category_id,category_name)
VALUES(7,'Water Purifiers');

INSERT 0 1

#COMMIT;

WARNING:  there is no transaction in progress
COMMIT

# SELECT * FROM product_categories;

 category_id |  category_name  
-------------+-----------------
           1 | Air Cooler
           2 | Television
           3 | Alexa
           4 | Washing Machine
           5 | Laptop
           6 | Refrigerator
           7 | Water Purifiers
(7 rows)

To start a transaction in PostgreSQL, use BEGIN TRANSACTION or simply BEGIN command. Such transactions usually persist until the next COMMIT or ROLLBACK command is encountered. Alternatively, you can also use END or END TRANSACTION command commit the changes and end the transaction.

Following are transaction control commands in PostgreSQL.

BEGIN [TRANSACTION] − To start a transaction.
COMMIT − To save the changes, alternatively you can use END [TRANSACTION] command.
ROLLBACK − To rollback the changes.
# BEGIN;

BEGIN

# SELECT * FROM product_categories;

 category_id |  category_name  
-------------+-----------------
           1 | Air Cooler
           2 | Television
           3 | Alexa
           4 | Washing Machine
           5 | Laptop
           6 | Refrigerator
           7 | Water Purifiers
(7 rows)

# INSERT INTO product_categories(category_id,category_name)
VALUES(8,'Vacuum Cleaner');

INSERT 0 1

# SELECT * FROM product_categories;

 category_id |  category_name  
-------------+-----------------
           1 | Air Cooler
           2 | Television
           3 | Alexa
           4 | Washing Machine
           5 | Laptop
           6 | Refrigerator
           7 | Water Purifiers
           8 | Vacuum Cleaner
(8 rows)

# ROLLBACK;

ROLLBACK

# SELECT * FROM product_categories;

 category_id |  category_name  
-------------+-----------------
           1 | Air Cooler
           2 | Television
           3 | Alexa
           4 | Washing Machine
           5 | Laptop
           6 | Refrigerator
           7 | Water Purifiers
(7 rows)
Was this article helpful to you? Yes No

How can we help?