cancel
Showing results for 
Search instead for 
Did you mean: 

Is there a way to directly map boolean columns in MTK?

Accepted Solution

Is there a way to directly map boolean columns in MTK?

I've been experimenting with migrating data from Oracle using the MTK tool, and am running into an issue with boolean columns.

Oracle doesn't have a native boolean datatype, so booleans as stored in a NUMBER(1) column, using the values 0 or 1.  I believe this is fairly idiomatic in the oracle world.  Postgres, of course, does have a native boolean datatype which we want to use.
When running MTK, i can, of course, use customColTypeMapping to map those columns in a native boolean column type in the DDL.
But the data transfer doesn't work, because you can't store the number 1 in a boolean field, and there is no implicit cast to boolean.

Right now, the only way I can get it to work is to migrate it as a numeric field, and doing an after-the-fact conversion of the data into boolean fields after all the data has been migrated.  Is there a way to directly import these fields as booleans?  I would imagine that would be much faster than importing and converting after the fact.

View Entire Topic
dhananjayjejur
EDB Team Member
Solution

Hi,

 

MTK does migrate schema and data successfully for the NUMBER(1) data type for Oracle to PostgreSQL permutation.
Following is the use case.
Source Oracle Version: Oracle 12c
Target Postgres Version: 12
 
1) Execute the following script in the source database,
create table test_bool (eid number, isManager NUMBER(1));
insert into test_bool values (1,1);
insert into test_bool values (2,1);
insert into test_bool values (3,0);
insert into test_bool values (4,1);
commit;
 
2) run the MTK using the following command,
./runMTK -sourcedbtype oracle -targetdbtype postgres -customColTypeMapping ismanager=boolean HR
 
3) Execute the following query on the target server.
select * from hr.test_bool;
 
You will be able to see the true and false values for this table.
 
Kindly let us know if you have any concerns or issues.
Ask a Question