Transaction in PostgreSQL will often confuse personnel coming from Oracle database world. In this chapter, we demonstrate you how DDL statements in PostgreSQL become part of transaction and muddles the concept of Oracle transaction.
OraclePostgreSQL
Only DML statements can be rolled back or committed within a transaction in Oracle. One DDL statement makes one transaction. DDL statement are committed automatically. You can not rollback a DDL statement.
Rollback DML
SQL> SELECT * FROM Dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> INSERT INTO Dept VALUES(50, 'HR', 'CANADA');
1 row created.
SQL> SELECT * FROM Dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 HR CANADA
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> ROLLBACK;
Rollback complete.
SQL> SELECT * FROM Dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTONIn PostgreSQL transaction, any type of statement can be committed or rolled back.