Migrate blank Oracle value ' ' to postgre empty string ''

[helenaadi]

Hi Gilles,

For our migration, we have an Oracle database. In this database we have blank value ' ' and we need to modify these data to an empty string in postgre ''. It's possible to add something in the configuration file of ora2pg ? Or something else ?

Thank you for your help ! Helena :)

[darold]

Hi Helena,

There is no magic configuration directive for that yet. I can think about adding a REPLACE_VALUE directive that can be defined to use REPLACE(column) in the Oracle query to extract the data. No magic unless you use the FDW data export export mode, in this case there is the ORACLE_FDW_TRANSFORM directive that can be used as follow:

ORACLE_FDW_TRANSFORM    TABLE[COLUMN_NAME, <replace code in SELECT target list>];...

Supposing that you want to apply the change to columns COL1 and COL2 on table TB1, the following might help:

ORACLE_FDW_TRANSFORM    T1[COL1, regexp_replace(COL1, '^ $', '')];T1[COL2, regexp_replace(COL2, '^ $', '')];

Otherwise the better solution is to import the blank as is and to run an update on the column.

[helenaadi]

There is a lot of tables and columns. If we add around 6000 lines of configuration in the file it will work ?

Thank you for your answer ;) Helena

[darold]

At this level I guess that all column with blank must be turned into empty string so better is to have a REPLACE_VALUE or BLANK_TO_EMPTY directive that can handle the case. The configuration file is not done to such huge transformation, theoretically it could work (slowly) but I won't bet my hand on this :-)

[darold]

Commit 0f2dabd adds TRANSFORM_VALUE configuration directive to be able to apply an expression when retrieving data from Oracle.

For your issue you could use:

TRANSFORM_VALUE         TEST_TRANSFORM[COL1:regexp_replace("COL1", '^ $', '')]

note that Oracle will return NULL instead of empty string.