Reverse PostgreSQL into model with undefined data types

Recently while importing a new physical schema from PostgreSQL 8.4 into Oracle Data Integrator(ODI) via JDBC driver all of the tables were reversing into the model without data types defined for text, double precision, smallint, integer and boolean.

If one or two fields are missing it’s easy enough to set the data type manually for the column, but for a whole mess of tables and thousands of fields it’s unrealistic.

The solution is to define the proper data types using the physical architecture tab with PostgreSQL internal names for the data types. Even though by default there is one for integer, looking at the data type definitions smallint maps to int2, integer maps to int4, double precision maps to float8, boolean maps to bool.

Using these aliases which are used internally by PostgreSQL for historical reasons in the ODI Physical Architecture will fix the missing data types during the reverse process.

Author’s Note

Initial md Generated using https://github.com/jsr6720/wordpress-html-scraper-to-md

Original Wordpress categories: [‘Oracle Data Integrator’]

Original Wordpress tags: “Oracle Data Integrator”, “data types”, “ODI”, “PostgreSQL”, “reverse”

Original Wordpress comments: None

Significant revisions

tags: 2011, wordpress, txcowboycoder, odi, oracle-data-integrator, data-types, postgreSQL

May 6th, 2024 Converted to jekyll markdown format and copied to personal site
Jun 30th, 2011 Originally published on txcowboycoder wordpress site