Using a different column name in a foreign table

From: Alanoly Andrews <alanolya(at)invera(dot)com>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Using a different column name in a foreign table
Date: 2022-01-21 16:24:14
Message-ID: YQXPR01MB3000CA608DF76835A6881B01AB5B9@YQXPR01MB3000.CANPRD01.PROD.OUTLOOK.COM
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Awaiting some input.

Alanoly Andrews.

This e-mail may be privileged and/or confidential, and the sender does not waive any related rights and obligations. Any distribution, 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. Toute diffusion, 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 a new disclaimer message.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Shaozhong SHI 2022-01-21 16:26:01 Re: Can commands be typed in to view geometry in PgAdmin?
Previous Message Laurenz Albe 2022-01-21 15:54:59 Re: Query on postgres_fdw extension