Re: Using a different column name in a foreign table

[Guillaume Lelarge <guillaume@xxxxxxxxxxxx>]

Hi,

Le ven. 21 janv. 2022 à 17:24, Alanoly Andrews <alanolya@xxxxxxxxxx> 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.