Home > Documentation > Oracle to PostgreSQL

After Oracle to PostgreSQL database migration is completed it is time for the most important phase of the entire process - verification of the resulting database. It includes validation of the following objects and entities:

• Table definitions
• Data
• Indexes
• Foreign keys
• Views

Table Definitions

To explore table definitions Oracle provides statement DESC table_name. PostgreSQL uses \d table_name for the same purpose. Oracle table definition is converted properly when each column has appropriate type, size and other attributes in the target PostgreSQL table. This article explores appropriate conversion for all Oracle types.

Data

Converted data can be validated by visual comparison of particular fragments in both Oracle and PostgreSQL tables. In order to get more comprehensive diagnostics, the data can be exported in CSV format and compared by standard text processing tools like FC (file compare).

Besides, it is reasonable to check that source and destination tables have the same number of records. Both DBMS use the following query to get number of rows in table:

SELECT COUNT(*) FROM table_name

Indexes

Conversion of indexes can be validated by verifying number of indexes, indexed columns and attributes for each index. Oracle allows to list indexes via this query:

SELECT * FROM all_indexes WHERE table_name = '{table name}'

PostgreSQL displays information about indexes at the bottom of CREATE TABLE statement generated by the command: \d table_name

Foreign Keys

Verification of foreign keys is similar to indexes. Oracle allows to extract foreign keys through this query:

SELECT 
    a.table_name,a.constraint_name,a.delete_rule,b.column_name 
FROM 
    user_constraints a, user_cons_columns b 
WHERE 
    a.constraint_name=b.constraint_name and a.constraint_type='R' 

PostgreSQL stores all necessary information about foreign keys in "information_schema" table, it can be queried as follows:

SELECT
    tc.constraint_name, tc.table_name, kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name 
FROM 
    information_schema.table_constraints AS tc 
    JOIN information_schema.key_column_usage AS kcu
      ON tc.constraint_name = kcu.constraint_name
    JOIN information_schema.constraint_column_usage AS ccu
      ON ccu.constraint_name = tc.constraint_name
WHERE constraint_type = 'FOREIGN KEY' AND tc.table_name='table_name';

Views

There is only way to validate migration of views, it is manual comparison SELECT-queries of each view in Oracle and PostgreSQL with respect to differences between SQL dialects of these two DBMS. Both Oracle and PostgreSQL list all available views using the query:

SELECT table_name FROM INFORMATION_SCHEMA.views;

Then each view can be explored in Oracle database like this:

SELECT text FROM all_views WHERE view_name='{name of view}'

PostgreSQL uses this query for the same purpose:

SELECT definition FROM pg_views WHERE viewname = '{name of view}'

Main differences between syntax of queries in Oracle and PostgreSQL are exposed in this article.