How to convert Oracle database to PostgreSQL
|[Oracle to PostgreSQL Converter] [Validate Migration Results] [Triggers] [Types Mapping] [Views]|
Oracle is a powerful object-relational DBMS suitable for handling huge enterprise scale databases. However, it has high cost of ownership and strict licensing policy. This valuable disadvantage forces some companies and organizations to migrate their databases from Oracle to another DBMS.
Database admin or another person responsible for database migration should expect that new system provides similar set of features compared to the original DBMS. When talking about Oracle database, it is obvious that none of other relational database management systems is so enriched with powerful features such as:
Being powerful standards-compliant RDBMS that combines object-oriented and relational database functionality, PostgreSQL gets much closer to Oracle than other systems. Here is the brief list of its advanced features:
The process of converting Oracle database to PostgreSQL consists of the following steps:
Below we consider basic steps from this list in more details. Anywhere below SQL*Plus is used as default Oracle client application. This is the command line to connect to the database via SQL*Plus:
First, it is necessary to get list of all tables:
SQL> select table_name from user_tables;
And this is how definition of particular Oracle table can be extracted:
SQL> set long 1000 SQL> set pagesize 0 SQL> select DBMS_METADATA.GET_DDL('TABLE','<TABLE NAME>'[,'SCHEMA']) from DUAL
The resulting script must be corrected before loading to PostgreSQL as follows:
Next step is to export Oracle data into CSV format. It can be done via the following sequence of commands:
SQL> set heading off SQL> spool filename.csv SQL> select column1 || ',' || column2 || ... from mytable; SQL> set colsep ',' SQL> select * from my_table; SQL> spool off;
The resulting CSV file can be imported into PostgreSQL table via "COPY" command:
COPY <table name> FROM <path to csv file> DELIMITER ',' CSV;
If there is "Permission denied" error after running this statement, try to use "\COPY" command instead.
Let's get list of all indexes that belong to table "mytable":
SQL> select * from all_indexes where table_name = '<TABLE NAME>';
Keep in mind that Oracle stores table names as upper case by default unless lower case was specified directly by enclosing table name in quotes in "CREATE TABLE" statement.
And this is how definition of particular indexes can be extracted:
SQL> set long 1000 SQL> set pagesize 0 SQL> select DBMS_METADATA.GET_DDL('INDEX','<INDEX NAME>') from DUAL;
Empty String vs NULL
Oracle does not distinguish empty strings from NULL values in string context. The concatenation of NULL and string obtain string as a result. In PostgreSQL the result of concatenation above is NULL.
Oracle uses operator
IS NULL to check if string is empty,
in PostgreSQL the same expression is FALSE for empty string and TRUE for NULL
CREATE SYNONYM statements to access remote objects.
PostgreSQL does not support
CREATE SYNONYM, it can set up
remote definition via
SET search_path. For example, in Oracle:
CREATE SYNONYM loc.tbl FOR rem.tbl;
In PostgreSQL the equivalent is:
SET search_path TO 'loc.tbl';
Although syntax of creating sequences in Oracle and PostgreSQL is quite similat, there is minor difference in getting next value. In Oracle:
The same in PostgreSQL is:
Have more questions? Contact us