search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
991 stars 342 forks source link

Data format of "INTERVAL" type incorrect for PG #1643

Closed shubham-yb closed 11 months ago

shubham-yb commented 1 year ago

Hi Team,

Please consider this example,

Oracle source schema:

CREATE TABLE interval_types(int_d_s INTERVAL DAY(9) TO SECOND(9));
INSERT INTO interval_types VALUES(TIMESTAMP '2001-09-03 12:47:00.000000' - TIMESTAMP '2001-09-03 13:13:00.000000');

This evaluates to -000000000 00:26:00.000000000 which is the correct value for Oracle and Ora2pg exports this value as is.

The behaviour for PG is different though. It takes the days and hh/mm/ss as different entities and has separate +/- signs for both. Thus the correct representation of this value for PG is -000000000 -00:26:00.000000000. Note the negative (-) before 00:26.

When we import the value exported i.e -000000000 00:26:00.000000000, PG understands it as -000000000 +00:26:00.000000000 thus resulting in the wrong value on the target.

Thank You for your help!

darold commented 11 months ago

Commit e19bc1f fixes this issue.