PostgreSQL

From Oracle FAQ
Jump to: navigation, search

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

Contents

[edit] 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.

[edit] Migration to Oracle

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

  • SQLWays - comprehensive, but expensive migration solution
  • SwissSQL - don't have the full functionality required

[edit] 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

[edit] 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

[edit] Also see

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

[edit] External links