PostgreSQL

PostgreSQL is an open source relational DBMS system. PostgreSQL runs on several platforms including Linux, Windows, HP-UX, Solaris and AIX.

Compared to Oracle

Of all open source databases available, PostgreSQL is the closest to Oracle. They even provide a PL/pgSQL language similar to PL/SQL to help developers to port applications from Oracle to PostgreSQL.

Migration to Oracle

Oracle doesn't offer any tools that can convert PostgreSQL databases to Oracle. The following third party solutions are, however, available:

• SwissSQL - don't have the full functionality required
• Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) automatically migrates the entire database schema (tables, views, stored procedures, functions, triggers, etc.) and transfers data from PostgreSQL to Oracle.

Migration from Oracle

Tools like Ora2pg and DBI-Link can be used to convert or interact with Oracle tables:

• Ora2pg - Perl module to export an Oracle schema to PostgreSQL
• DBI-Link - uses Perl's DBI module to link to remote data sources (like Oracle) and treat them as PostgreSQL tables
• Ispirer MnMTK Ispirer Migration and Modernization Toolkit (Ispirer MnMTK) performs automatic migration of Oracle to any popular RDBMS.

ERROR: current transaction is aborted, commands ignored until end of transaction block

Unlike Oracle, PostgreSQL will abort the ENTIRE transaction if a single statement in it fails. As a workaround, one must use SAVEPOINTs to rollback the failed statement and continue the transaction. To demonstrate the problem:

postgres=# CREATE TABLE t1 (id INT PRIMARY KEY);
postgres=# BEGIN;
BEGIN
postgres=# INSERT INTO t1(id) VALUES(1); 
INSERT 0 1
postgres=# INSERT INTO t1(id) VALUES(1);
ERROR:  duplicate key value violates unique constraint "t1_pkey"
postgres=# SELECT * FROM t1;
ERROR:  current transaction is aborted, commands ignored until end of transaction block

Also see

• MySQL - open-source database
• EnterpriseDB - proprietary extensions on top of PostgreSQL (provides Oracle compatibility)

External links

• PostgreSQL Home Page