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.

Accepted Solutions (1)

Accepted Solutions (1)

EDB Team Member



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);
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.

Answers (0)

Archived Discussions

Effective March 31st, we will no longer engage on PostgresRocks.

How to engage with us further?

  • Thought Leadership: EDB Blogs

  • Tips and Tricks: Postgres Tutorials

  • Customer Support: Create a Case Please note: Only customers with an active EDB support subscription and support portal authorization can create support ticket

  • Engage on Stackoverflow While engaging on Stackoverflow tag the question with EDB or EnterpriseDB.

© 2019 EnterpriseDB Corporation. All rights reserved.   |   Privacy Policy   |  Terms of Use   |   Trademarks