Foreign Table datatypes
Hello!
I'm trying to create some foreign tables to a PostgreSQL database. In some cases, columns with certain datatypes cannot be consumed by IRIS and the following error is thrown:
[SQLCODE: <-237>:<Schema import for foreign table did not return column metadata>]
[%msg: <Unkown data type returned by external database>]
For example: serial4 typed ID columns are typical examples. Is it possible, what's the best way of resolving these datatypes, which- seemingly- don't have proper JDBC metadata mappings?
Of course, explicitely defining columns and types as part of the CREATE FOREIGN TABLE statement solves the problem, but when working with large number of foreign tables and unknown number of potentially problematic datataypes, this can be cumbersome.
Thank you for any help in advance!
Attila Toth
Comments
The documented options for this situation are:
- If IRIS can read the external JDBC metadata, omitting the column list lets it automatically import the schema from the external table. [1]
- If schema validation/import is a problem, you can define the foreign table with an explicit column list and InterSystems SQL types; data from the external source is then coerced into the specified type. [1]
- If you do not want IRIS to validate the external schema at create time, you can add
NOVALIDATEtoCREATE FOREIGN TABLE. [1]
So for unsupported or unrecognized PostgreSQL types such as the case you described, the documented workaround is to define the columns yourself using valid InterSystems SQL data types, rather than relying on automatic schema import. [1]
You can also adjust types afterward with ALTER FOREIGN TABLE, which allows changing one or more column data types to valid InterSystems SQL data types. [2]
Note: Foreign tables do not currently support accessing stream-typed data from an external data source. [3]
The returned documents do not provide a documented mechanism to extend or customize JDBC type metadata mapping for unknown external types such as serial4, nor a built-in bulk auto-resolution process for all such unmapped types. Based on the available documentation, explicit type definition or later type adjustment is the supported path. [1][2]
Sources: