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:
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 DATE
s 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
.