Due to the modern trend of switching to open-source software as an opportunity to cut the licensing fees, many companies migrate their databases from Oracle to PostgreSQL.Every database specialist knows it is very important to verify that all database objects have been processed properly after the migration is completed. This guide explores main steps of the verification. Migration of database business logic such as stored procedures, functions and triggers is not covered by the whitepaper.
Table Definitions
Oracle allows to explore table definition by running SQL query:
select COLUMN_NAME, DATA_DEFAULT, DATA_TYPE from USER_TAB_COLUMNS where table_name=’your table name’
PostgreSQL can do the same by running the statement:\d table_name
We can say that Oracle table is migrated properly when each column hasequal type, size, NULL check and default value in the target PostgreSQL table. This table sillust rates correct type mapping for Oracle and PostgreSQL.
| Oracle | PostgreSQL |
| BFILE | VARCHAR(255) |
| BINARY_FLOAT | REAL |
| BINARY_DOUBLE | DOUBLE PRECISION |
| BLOB | BYTEA |
| CHAR(n), CHARACTER(n) | CHAR(n), CHARACTER(n) |
| CLOB | TEXT |
| DATE | TIMESTAMP |
| DECIMAL(p,s), DEC(p,s) | DECIMAL(p,s), DEC(p,s) |
| DOUBLE PRECISION | DOUBLE PRECISION |
| FLOAT(p) | DOUBLE PRECISION |
| LONG | TEXT |
| LONG RAW | BYTEA |
| NCHAR(n) | CHAR(n) |
| NCHAR VARYING(n) | VARCHAR(n) |
| NCLOB | TEXT |
| NUMBER(p,0), NUMBER(p), 1 <= p < 5 | SMALLINT |
| NUMBER(p,0), NUMBER(p), 5 <= p < 9 | INT |
| NUMBER(p,0), NUMBER(p), 9 <= p < 19 | BIGINT |
| NUMBER(p,0), NUMBER(p), p >= 19 | DECIMAL(p) |
| NUMBER(p,s), NUMERIC(p,s) | DECIMAL(p,s) |
| NUMBER, NUMBER(*) | DOUBLE PRECISION |
| NVARCHAR2(n) | VARCHAR(n) |
| RAW(n) | BYTEA |
| REAL | DOUBLE PRECISION |
| ROWID | CHAR(10) |
| SMALLINT | SMALLINT |
| TIMESTAMP(p) | TIMESTAMP(p) |
| TIMESTAMP(p) WITH TIME ZONE | TIMESTAMP(p) WITH TIME ZONE |
| VARCHAR2(n) | VARCHAR(n) |
| XMLTYPE | XML |
Let us illustrate migration of table definition from Oracle to PostgreSQL by the following example. Assume, the Oracle table is declared as:
CREATE TABLE people(
id NUMBER(10) NOT NULL,
added DATE,
data BLOB,
commentsCLOB,
PRIMARY KEY(id)
);
PostgreSQL equivalent of this table definition is:
CREATE TABLE people(
id BIGINT NOT NULL,
added TIMESTAMP,
data BYTEA,
commentsTEXT,
PRIMARY KEY(id)
);
Data
The first thing that must be checked here is Oracle and PostgreSQL tables have equal number of records. Both database management systems allow to obtain number of rows in a table through the following query:
SELECT COUNT(*) FROM table_name
Next step of data verification is visual comparison of a random Oracle and PostgreSQL data fragments. Oracle allows to explore fragment of data as follows:
SELECT * FROM table_name OFFSET start_record ROWS FETCH NEXT number_of_records ROWS ONLY;
PostgreSQL supports similar syntax of SELECT-query:
SELECT * FROM table_name LIMIT number_of_records OFFSET start_record
Indexes
Validation of migrated indexes consists of checking a number of Oracle and PostgreSQL indexes per table and comparing indexed columns and attributes of each source and target index.
Oracle provides two queries to get information about primary key and other indexes. For primary key:
SELECT DISTINCT a.constraint_name, a.column_name FROM user_cons_columns a,
user_constraints b WHERE a.table_name='{table name}’ AND
a.constraint_name=b.constraint_name AND b.constraint_type=’P’
ORDER BY a.constraint_name
For other indexes:
SELECT DISTINCT user_indexes.index_name, user_indexes.uniqueness,
user_ind_columns.column_name FROM user_ind_columns,
user_indexes where user_ind_columns.table_name = ‘{table name}’ AND
user_indexes.generated=’N’ AND user_ind_columns.index_name = user_indexes.index_name
AND user_ind_columns.table_name = user_indexes.table_name
ORDER BY user_indexes.index_name
PostgreSQL exposes indexes at the end of table description that is output of the statement\d {name_of_table},for example:
tests=# \d tbl1;
Column | Type | Modifiers
———+—————————–+——————-+———————————
id | integer | not null | nextval(‘tbl1_id_seq’::regclass)
added | timestamp without time zone | |
data | bytea | |
length |bigint | |
Indexes:
“tbl1_pkey” PRIMARY KEY, btree (id)
“idx1” btree (added)
Foreign Keys
Approach to verification of migrated foreign keys is very close to indexes. Oracle exposes information about foreign keys through the query:
SELECT DISTINCT a.constraint_name, a.column_name FROM user_cons_columns a,
user_constraints b WHERE a.table_name='{table name}’ AND
a.constraint_name=b.constraint_name AND b.constraint_type=’R’
ORDER BY a.constraint_name
PostgreSQL allows to extract data on foreign keys from service table “information_schema”:
SELECT
t_c.constraint_name, t_c.table_name, k_c_u.column_name,
c_c_u.table_name AS foreign_table_name,
c_c_u.column_name AS foreign_column_name
FROM
information_schema.table_constraints AS t_c
JOIN information_schema.key_column_usage AS k_c_u
ON t_c.constraint_name = k_c_u.constraint_name
JOIN information_schema.constraint_column_usage AS c_c_u
ON c_c_u.constraint_name = t_c.constraint_name
WHERE constraint_type = ‘FOREIGN KEY’ AND t_c.table_name=’table_name’;
Views
Checking migration of Oracle views into PostgreSQL format is the most sophisticated part of overall validation.It is made by comparing CREATE VIEW statement of every view with respect to differences between SQL dialects of source and destination DBMS.
Oracle exposes list of all views in the database using the query:
select VIEW_NAME, TEXT from SYS.USER_VIEWS;
PostgreSQL can do the same via the query:
select viewname, definition from pg_catalog.pg_views where schema name NOT IN (‘pg_catalog’, ‘information_schema’)
Check that none of Oracle specific keywords appear in PostgreSQL CREATE VIEW statement:
- DEFAULT
- FORCE / NO FORCE
- WITH CHECK OPTION
- WITH OBJECT IDENTIFIER
- WITH READ ONLY
- UNDER
Next thing to be verified is that Oracle specific notation of JOIN operators is converted into PostgreSQL standard properly. For example, LEFT JOIN statement in Oracle may be composed as:
SELECT t1.f2, t2.f2 FROM t1, t2 WHERE t1.f1=t2.f1 (+)
PostgreSQL accepts only ANSI SQL standard on LEFT JOIN that is:
SELECT t1.f2, t2.f2 FROM t1 LEFT OUTER JOIN t2 ON t1.f1=t2.f1
RIGHT JOIN in Oracle may be composed using operator (+) as follows:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliers, orders
WHERE suppliers.supplier_id(+) = orders.supplier_id;
In PotgreSQL the same query must be re-written as:
SELECT orders.order_id, orders.order_date, suppliers.supplier_name
FROM suppliersRIGHT OUTER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;
Besides the differences listed above, queries in Oracle and PostgreSQL are distinguished due to different sets of embedded functions.When migrating views every Oracle function must be converted into PostgreSQL equivalent as it is specified in table below.
| Oracle | PostgreSQL |
| dbms_random.value | RANDOM() |
| INSTR($str1, $str2) | POSITION($str2 in $str1) |
| LCASE ($a) | LOWER($a) |
| NVL($a, replace_with) | COALESCE($a, replace_with) |
| SYSDATE | CURRENT_DATE |
| UCASE($a) | UPPER($a) |
| SYS_EXTRACT_UTC($date) | CAST($date at time zone ‘utc’ AS timestamp) |
More articles about Oracle to PostgreSQL migration can be found at: https://www.convert-in.com/docs/ora2pgs/contents.htm
