Re: Using a different column name in a foreign table

Hi,

Le ven. 21 janv. 2022 à 17:24, Alanoly Andrews <alanolya(at)invera(dot)com> a
écrit :

> Hello,
>
> I see that the syntax for the creation of a foreign table allows you to
> use a column name in the FT that is different from the one in the base
> table. Such a "create foreign table" statement executes successfully and
> creates the FT. But when I query the FT, I get an error wrt to the column
> that had been renamed. See example below:
>
> create foreign table tab1_ft (
> id int,
> name char(10) options(column_name 'newname'))
> server xxxxxx
> options(schema_name 'public', table_name 'tab1');
>
> select * from tab1_ft;
>
> ERROR: column "newname" does not exist
> HINT: Perhaps you meant to reference the column "tab1.name".
> CONTEXT: Remote SQL command: SELECT id, newname FROM public.tab1
>
> So, it seems that the when the remote SQL command is composed, the mapping
> of 'newname' to the 'name' in the base table does not take effect.
> Is there a resolution to this issue?
>
>

Your foreign table definition should have the new column name. You did it
the other way around. This is how you should have done it:

create foreign table tab1_ft (
id int,
newname char(10) options(column_name 'name'))
server xxxxxx
options(schema_name 'public', table_name 'tab1');

Regards.

--
Guillaume.