Thanks Tom, Guillaume and Adrian.
My mistake in reversing the order of name and newname in the definition.
After the switch, it works.
But....I'm investigating another environment where the definition appears to have been correct and yet there is an
errorin select. Maybe I'll post again later, if needed.
Thanks.
A.A.
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, January 21, 2022 11:52 AM
To: Alanoly Andrews <alanolya@invera.com>
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Using a different column name in a foreign table
[Email External/Externe] Caution opening links or attachments/attention lors de l'ouverture de liens ou de pièces
jointes.
Alanoly Andrews <alanolya@invera.com> writes:
> 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
fromthe one in the base table. Such a "create foreign table" statement executes successfully and creates the FT. But
whenI 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
tabledoes not take effect.
Huh? The CONTEXT line shows what was issued to the remote server, and it's very obvious that we *are* asking for
"newname",as indeed is also implied by the error issued by the remote. I think you just didn't match the name
correctlyto the actual name on the remote.
regards, tom lane
This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations.
Anydistribution, use or copying of this e-mail or the information it contains by other than an intended recipient is
unauthorized.If you received this e-mail in error, please advise me (by return e-mail or otherwise) immediately.
Ce courriel est confidentiel et protégé. L'expéditeur ne renonce pas aux droits et obligations qui s'y rapportent.
Toutediffusion, utilisation ou copie de ce message ou des renseignements qu'il contient par une personne autre que le
(les)destinataire(s) désigné(s) est interdite. Si vous recevez ce courriel par erreur, veuillez m'en aviser
immédiatement,par retour de courriel ou par un autre moyen.'. If the disclaimer can't be applied, attach the message to
anew disclaimer message.
