Issue with selecting column from long/complex table about oracle_fdw HOT 3 CLOSED

The JAVA (JDBC) fdw fails with the same error.

I guess it's not an oracle_fdw problem.

The problem actually appears to be that when you do this:

==> create foreign table tib (INCIDENT_ID numeric) server rac options (schema 'GC',table 'TIB');

it doesn't in fact operate the conversion on the INCIDENT_ID (second) column, but on the FIRST column, which is a DATE type. DATE can't be converted to NUMERIC so it fails.

As this happens in the JDBC one as well, either I'm misunderstanding how this is supposed to work, or there's something in the foreign table mechanism that's always starting from the first column and going by position, rather than by matching column names.

from oracle_fdw.

Your suspicion is right; oracle_fdw does not match columns by name, but rather by column position.
The main reason is that PostgreSQL 9.1 (which is supported by oracle_fdw) does not allow column options,
and automatic matching of names is difficult (because Oracle's names default to upper case, while
PostgreSQL's default to lower case).

I have documented that in the README, did you find the description misleading or insufficient?

As a workaround I recommend that you create a foreign table with two columns and use the second,
but your solution with a view on the Oracle side is just as good.

Yours,
Laurenz Albe

from oracle_fdw.

Sorry, I only just found the part in the README that says it's mapped by position.

I think my comprehension problem comes from the example seeming to imply a name mapping (well, to me anyway).

Sorry for the issue, you can close this.

from oracle_fdw.