I have a requirement to migrate data from AWS RDS Oracle DB instance to on-prem PostgreSQL DB,
I have manually created DB tables in PostgreSQL (replica of Oracle tables) now I’m trying to load the data, the approach I’m using is:
- connect to oracle sql developer tool
- click on table name
- select export
- export data check box checked format is insert
- then finally export
This gives me a bunch of insert statements like if there are 10 rows of data then I’ll get 10 insert statements, then I take those insert statements and execute in PostgreSQL via pgadmin.
But the problem comes with date, time, timestamp datatypes. For example in oracle:
date : 01-SEP-22,
time_stamp: 17-MAR-23 09.36.33.000000000 AM
In PostgreSQL:
date : 2022-09-01,
time_stamp : 2023-05-03 15:36:32.74049
Please let me know the best approach to fix this issue and migration data migration?
In SQL Developer, before exporting the data, change the NLS_DATE_FORMAT, NLS_TIMESTAMP_FORMAT and NLS_TIMESTAMP_TZ_FORMAT session parameters to your desired format:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF';
-- ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZH:TZM';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF TZR';
Then when you run the export it should generate dates/timestamps in ISO8601 format.
Note: In Oracle, a DATE is a binary data-type that consists of 7 bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has those components so it always has both date and time components (even if the client application you are using to display the data may chose to only display the date component and hide the time component – the time component still exists). If you store DATEs where the time component is always midnight then you can use:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
Otherwise, you probably do not want to migrate Oracle DATE to PostgreSQL DATE and want to migrate them to TIMESTAMP instead, so that you retain the time component of the Oracle DATE.
1